Jan
19
2016

Fix: Cannot drop server because it is used as a Distributor in replication

While trying to drop a linked server received following error,

 

Drop failed for LinkedServer ‘XYZ.ABC.COM’. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.6000.34+((KJ_SP3).140819-1214+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+LinkedServer&LinkId=20476
—————————–
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
Cannot drop server ‘XYZ.ABC.COM’ because it is used as a Distributor in replication. (Microsoft SQL Server, Error: 20581)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.6525&EvtSrc=MSSQLServer&EvtID=20581&LinkId=20476

After going through online posts, found following script to drop distributor settings on Pinal Dave site (link),

 

EXEC
sp_dropdistributor
@no_checks = 1, @ignore_distributo
r = 1

GO

 

After executing this, drop linked server completed with success,

 

/****** Object: LinkedServer [XYZ.ABC.COM] Script Date: 01/19/2016 13:28:43 ******/

IF
EXISTS
(SELECT srv.name FROM

sys.servers srv WHERE srv.server_id != 0 AND srv.name =
N’
XYZ.ABC.COM)EXEC
master.dbo.sp_dropserver
@server=N’
XYZ.ABC.COM, @droplogins=‘droplogins’

GO

 

Command(s) completed successfully.

 

Hope this helps.

Download PDF

About the Author: Nitin G

Indian born, trekker, biker, photographer, lover of monsoons. I've been working full time with SQL Server since year 2005 and blogs to post the content aquired during my research on new topics or fixing issues faced by me as a DBA while working in different kind of projects, hope some of my posts may helps others in SQLDBA community. Everything you read on my blog is my own personal opinion and any code is provided "AS-IS" with no warranties!

Leave a comment

Subscribe to this blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 457 other subscribers

Translate this blog!

EnglishFrenchGermanItalianPortugueseRussianSpanish

Calender

August 2017
M T W T F S S
« May    
 123456
78910111213
14151617181920
21222324252627
28293031  

View Post by Categories

%d bloggers like this: