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)
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’
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