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
For me, second option worked. Thanks aagain..
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’
Thank you Sharky, that’s what did it for me
Yes This works fine.
I tried but
Cannot drop server ‘test’ because it is used as a Publisher in replication.
A database should not be part of a replication process. It should be release from there in order to be dropped
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
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.
thanks so much! option 2 worked for me…
Nice one – first post I have found that solved this highly annoying problem!
Second solution worked great. Thanks for the post.
I used your second solution and fixed my problem completely!
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
The second solution worked for me as well. Thanks you, such a life saver.
You are Gr8.
Second Option Helped in My Case also.
Thanks.
second one worked for me pinal, Thank you alot for distributing your knowledge.
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
The second option saved me. Thank you so much!
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
I had the same problem. If you found any solution please send me when you have a time.
Thank you..
Thanks Rishi, your suggestions works nice…
The second option Saved me. Thanks a lot
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!