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)
61 Comments. Leave new
can’t we allow multiple server link with server with distributed transaction ????
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
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
this worked for me, thank you.
EXEC sp_dropdistributor @no_checks = 1, @ignore_distributor = 1
GO
you are awesome.
Worked for me. Thank you Pinal :)
secondone is worked for me, paji tussi great ho
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
This work for me.
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
thanks Pinal