SQL SERVER – ERROR: FIX: Cannot drop server because it is used as a Distributor in replication

« SQL SERVER – Future of Business Intelligence

SQLAuthority News – Featured on Channel 9 »

SQL SERVER – ERROR: FIX: Cannot drop server because it is used as a Distributor in replication

December 11, 2009 by pinaldave

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)

Posted in Pinal Dave, SQL, SQL Authority, SQL Error Messages, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | Tagged SQL Replication | 10 Comments

10 Responses

on August 17, 2010 at 5:35 am | Reply Prashanth

For me, second option worked. Thanks aagain..
on December 21, 2010 at 1:28 pm | Reply Sharky

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’
on August 22, 2011 at 7:24 pm | Reply KK

Yes This works fine.
on October 26, 2011 at 12:42 am | Reply bu

I tried but
Cannot drop server ‘test’ because it is used as a Publisher in replication.
- on November 3, 2011 at 2:14 pm | Reply madhivanan
  
  A database should not be part of a replication process. It should be release from there in order to be dropped
on October 31, 2011 at 2:44 pm | Reply sushil

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
on November 10, 2011 at 12:55 am | Reply bu

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.
on November 23, 2011 at 11:42 am | Reply bcbee

thanks so much! option 2 worked for me…
on December 1, 2011 at 3:26 pm | Reply dan

Nice one – first post I have found that solved this highly annoying problem!
on December 7, 2011 at 2:33 am | Reply JMT

Second solution worked great. Thanks for the post.