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.

Fix/Workaround/Solution:

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'
GO

EXEC sp_dropdistributor @no_checks = 1, @ignore_distributor = 1
GO

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)

About these ads

20 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
    @optvalue=N’true’

    should be
    @optvalue=N’false’

  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
    GO

  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

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