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

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)

, ,
Previous Post
SQL SERVER – Future of Business Intelligence
Next Post
SQLAuthority News – Featured on Channel 9

Related Posts

59 Comments. Leave new

  • For me, second option worked. Thanks aagain..

    Reply
  • 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’

    Reply
  • Yes This works fine.

    Reply
  • I tried but
    Cannot drop server ‘test’ because it is used as a Publisher in replication.

    Reply
    • A database should not be part of a replication process. It should be release from there in order to be dropped

      Reply
  • 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

    Reply
  • 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.

    Reply
  • thanks so much! option 2 worked for me…

    Reply
  • Nice one – first post I have found that solved this highly annoying problem!

    Reply
  • Second solution worked great. Thanks for the post.

    Reply
  • I used your second solution and fixed my problem completely!

    Reply
  • Peter Hiovany Fonseca Buitrago
    June 5, 2012 8:53 am

    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

    Reply
  • The second solution worked for me as well. Thanks you, such a life saver.

    Reply
  • You are Gr8.
    Second Option Helped in My Case also.

    Thanks.

    Reply
  • second one worked for me pinal, Thank you alot for distributing your knowledge.

    Reply
  • 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

    Reply
  • The second option saved me. Thank you so much!

    Reply
  • 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

    Reply
  • Thanks Rishi, your suggestions works nice…

    Reply
  • The second option Saved me. Thanks a lot

    Reply
  • 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!

    Reply

Leave a Reply Cancel reply

Exit mobile version