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. SQL Server refuses to drop a server used as a Distributor in replication, but the fix is quick.
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.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
61 Comments. Leave new
can’t we allow multiple server link with server with distributed transaction ????
You can use DTC. (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