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 (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – Future of Business Intelligence
Next Post
SQLAuthority News – Featured on Channel 9

Related Posts

58 Comments. Leave new

  • can’t we allow multiple server link with server with distributed transaction ????

    Reply
  • Hello Pinal Thanks for this good trick
    By the way you put the command :
    EXEC master.dbo.sp_serveroption @server=N’XXXX’, @optname=N’dist’, @optvalue=N’true’

    but i guess you meant @optvalue=N’False’ versus True
    At least that’s the way that worked for me

    Merci

    Reply
  • Hi Pinal Dave, the following script helped me to resolve the error that i got while Droping one of our UAT linked server which was pointing to distrubtion server that is no more exist.

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

    Reply
  • this worked for me, thank you.
    EXEC sp_dropdistributor @no_checks = 1, @ignore_distributor = 1
    GO

    Reply
  • Suraj Kumar Kshirsagar
    November 22, 2019 10:19 pm

    you are awesome.

    Reply
  • Worked for me. Thank you Pinal :)

    Reply
  • secondone is worked for me, paji tussi great ho

    Reply
  • I have to kill an active SPID using below query
    SELECT spid FROM sys.sysprocesses WHERE dbid = db_id(‘distribution’)
    then delete via GUI, I am using SSMS 16 on SQL server 2016

    Reply

Leave a Reply

Menu