Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLSailor.com

Anup SivaDas is a Lead Engineer and Microsoft SQLServer professional with iGATE, having a total IT experience of more than 8 years. Anup is an active blogger with SQLSailor.com, and can also be found on MSDN SQLServer forums and BeyondRelational.com. He has handled multiple SQLServer projects for various fortune 500 companies, and gained enrich proficiency within Database Administration, Consulting, Virtualization,Build, Run and Production Support activities. Blog | Twitter | LinkedIn

Tale of detaching a database which was enabled for replication!

In development environments I prefer to use ‘detach’ database option to move around databases. It’s simple, fast and easy.

Almost all the time when I detached a database earlier the feeling was like something which I mentioned before “Simple, fast and easy”, however today it was more sort of a fun experience. Here is that tale for you all.

Today I had to ‘detach’ a database from one of my development environments and straight away got this error !

 

 

[Pic courtesy : temzlandia.blogspot.com ]

The database was setup for replication, hence it cannot be dropped.

Apparently, my next step was to remove the publication which was setup for this database and the tale begins from this point.

Note - This was a very controlled dev environment and action like detaching, removing replication was not going to harm anything. In case you are going to repro this demo, then I would recommend you to do this on your lab environment.

I selected the required publication and went ahead with delete operation. The wizard was double checking with me if I was sure. I said Yes to it !

Bang! here comes the next error

The error was pointing that there are some connection issues to the Distributor or to the Subscriber. 

The distributor and the subscriber for this publication were already retired servers and not online and removing publication requires a connection to be made to the distributor/subscriber servers too. As those servers were not reachable, the wizard couldn’t proceed.

There is no way you could delete this publication directly using SSMS and the best way to do this is via system stored procedures.

I had to use 3 system stored procedures to cleanup this publication and those are -

sp_dropsubscription

sp_droppublication

sp_replicationdboption

The scripts used for the clean up processes are mentioned below -

--Cleans up the subscriptions for a publisher

USE <Your Database>
GO
EXEC sp_dropsubscription @publication = N'Your_Publication', 
@article = N'all', @subscriber = N'all', @destination_db = N'all', @ignore_distributor = 1

--Cleans up the publication

USE <Your Database>
GO
EXEC sp_droppublication @publication = N'Your_Publication', @ignore_distributor = 1
--Set Replication database option to false

USE Master
GO
EXEC sp_replicationdboption @dbname = N'Your_Publication', @optname = N'publish', @value = N'false'
,@ignore_distributor = 1

If you note the scripts you can find that they all have an argument @ignore_distributor passed as 1.All the three scripts were failing without this as the connection to distributor was not available as it was offline.

I was curious to check what exactly @ignore_distributor does, however the below BOL entry didnt gave me any clue -

@ignore_distributor = ] ignore_distributor

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

It’s very much clear that removing a publication will check connections to the Distributor and in case it’s not available for connection, there will be connection errors.

After running the above mentioned stored procedures I could proceed and detach the database.

Thanks for reading.


Comments

Leave a comment on the original post [sqlsailor.com, opens in a new window]

Loading comments...