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
If these don’t work try a simple
EXEC sp_who2
Find the process running on the ‘distribution’ DB.
KILL spid#
for me,the second option worked just fine. Thanks a lot
Thanks. It work for me on the option 2.
Henry – thanks for sharing.
thank you very much.
But i need to invite you in our country to help us in sql server 2012 or you can invite us to meet you
my e-mail is :[removed]
I have a situation Pinalbhai. I have a customer who needs to move his published database , subscriber database to another server. And the moment the move is complete, he wants the replication to resume like it never changed the server. I have been able to move all the databases to a point in time, but the replication runs into all sorts of problems.
Thank you so much. The second one worked for me.
wy – I am happy that it helped you.
Thanks Pinal ,
i got my problem resovle
Msg 207, Level 16, State 1, Procedure sp_dropdistributor, Line 114
Invalid column name ‘publisher_type’.
Can someone please help me on this??
Thank you so much in advance.
Thanks,2nd one is worked
Thanks.. this sorted my problem.. :-)
Hi 2nd one worked for me but it took around 1 hr to resolve on my development server
Notify me via email for new post for Transactional replication
Excellent !! EXEC sp_dropdistributor @no_checks = 1, @ignore_distributor = 1
GO this worked for me..
Yo tenia problemas con eliminar un Linked Server, y utilice el siguiente codigo:
EXEC sp_dropdistributor @no_checks = 1 , @ignore_distributor = 1
GO
Y esto me soluciono el problema.
EXEC sp_dropdistributor @no_checks = 1, @ignore_distributor = 1
GO worked for me. Many thanks
Great help Pinal!!
the 2nd option worked for me.
Thank you so much.
Hey Pinal,
Thanks for you help, I really was spending a long time to solve it,
;)
Thank you bro, I tried directly 2nd option it worked for me.
After run this procedure, then did following.
select @@SERVERNAME
sp_dropserver ‘OLDSERVERNAME’
sp_addserver ‘NEWSERVERNAMEINSTANCE’,’local’
then restarted the SQL Service. It is ok now.
Hello, can anyone help me with the below error;- When i am creating a distributor it does not allow me.
Msg 21105, Level 16, State 1, Procedure sp_adddistributor, Line 42
This edition of SQL Server cannot act as a Publisher or Distributor for replication.
Error message is self-explanatory. Check Select @@version and check the edition.
The second option worked perfectly
Great. Thanks @Julio
Number 2 worked well thanks.