SQL SERVER – ERROR: FIX: Cannot drop server because it is used as a Distributor in replication

Replication has been my favorite subject when it comes to resolving errors. I have found that many DBAs are stuck with the solving of the problem of replication for hours; however, the solution is very easy. One of the very common errors in replication occurs when replication is removed from any server. I have seen the following error as one attempts to remove replication from the same server when the publisher and distributor are on the same server.

Cannot drop server ‘repl_distributor’ because it is used as a Distributor in replication.

Cannot drop the distribution database ‘distribution’ because it is currently in use.


Try any of the following option. Either of them should work for you.

EXEC master.dbo.sp_serveroption @server=N'XXXX', @optname=N'dist', @optvalue=N'true'

EXEC sp_dropdistributor @no_checks = 1, @ignore_distributor = 1

Please do let me know your feedback regarding which one of the above options worked for you or if any of the other solution helped to resolve this issue.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

29 thoughts on “SQL SERVER – ERROR: FIX: Cannot drop server because it is used as a Distributor in replication

  1. I think here
    EXEC master.dbo.sp_serveroption @server=N’XXXX’, @optname=N’dist’, @optvalue=N’true’

    instead of

    should be


  2. none of them work for me…actually i deleted the distribution database without removing the replication. now trying to add new distributor but fully struck i am..pls help regarding this


  3. I have database backup (done using Microsoft DPM) and I restored this database into different server. When I try to create publisher it throws an error distribution database doesn’t exists in sysdatabases and at any degree I can’t establish the replication.


  4. Gracias tengo un Cluster Activo/Pasivo con Verytas Cluster y tenia este error al tratar de cambiar el nombre, y con esto solucione mi inconveniente. Gracias


  5. if the scripts give you error then try this. You have to kill the process and then drop the

    SELECT spid FROM sys.sysprocesses WHERE dbid = db_id(‘distribution’)

    KILL 52

    EXEC sp_dropdistributor @no_checks = 1, @ignore_distributor = 1


  6. Hi, I can´t fix the problem. I used the two options but not work. have these error
    Msg 207, Level 16, State 1, Procedure sp_dropdistributor, Line 106
    Invalid column name ‘publisher_type’.
    Help me


  7. In my scenario, the Distribution database was corrupt and can not be loaded at SQL Server service startup. Moreover the distribution database was dropped (even though the database files are there, corrupted). The first statement saved the day. I could disable the distribution and reconfigured it. Thanks indeed!


  8. I have a situation Pinalbhai. I have a customer who needs to move his published database , subscriber database to another server. And the moment the move is complete, he wants the replication to resume like it never changed the server. I have been able to move all the databases to a point in time, but the replication runs into all sorts of problems.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s