SQL Server DBA / sqlLAB / Uncategorized

Removing Replication

Sticking with the theme of “following my nose“- I set about removing replication from my new ReplicationExample sqlLab.

Method 1

While replication is still active, you can remove replication by right clicking Replication and selecting “Disable Publishing and Distribution“. This will remove the subscriptions, publications and also remove the distribution database. Couldn’t be easier.

Method 2

Alternatively you can disable replication by running:

 EXECUTE sp_removedbreplication @dbname='<database>'; 

Run this code against the subscriber and publisher to disable replication.

Problem:

Someone has restored a database from production into test. Unfortunately, this often creates orphaned replication artefacts that need to be cleaned up.

For this scenario, I have created a ExampleOrphanedReplication_sqlLab, as shown below. Notice the publication, ‘throwAwayPublication’, the existance of the distribution database, and no subscriptions.

If you now try to drop the publication, you get the following errors:

use [womReplication];
go

exec sp_droppublication @publication='throwAwayPublication'

-- OUTPUT:
-- Msg 208, Level 16, State 1, Procedure sp_MSdrop_publication, Line 278
-- Invalid object name 'dbo.MSmerge_subscriptions'.
-- Msg 266, Level 16, State 2, Procedure sp_MSdrop_publication, Line 278
-- Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current   -- count = 2.
-- Msg 266, Level 16, State 2, Procedure sp_MSrepl_droppublication, Line 0
-- Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current -- count = 1.
-- Msg 266, Level 16, State 2, Procedure sp_droppublication, Line 0
-- Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current -- count = 1.

Alternatively, if you attempt to run the “Disable Publishing and Distribution” wizard, it will error out with errors about not being able to find subscription details. So how do you forcefully remove the publications and the distributor database?

Step 1:  Drop the Publication with @ignore_distributor=1

use womReplication;
go

exec sp_droppublication @publication='throwAwayPublication',
                        @ignore_distributor=1

Step 2: Drop the distributor database with @ignore_distributor=1

use master;
go
exec sp_replicationdboption @dbname='womReplication',
                            @optname = 'publish',
							@value = 'false'

exec sp_dropdistributor @no_checks=1, @ignore_distributor = 1

Step 3: Finally you can run sp_removedbreplication, just to confirm that all went smoothly

use master;
go
exec sp_removedbreplication @dbname='womReplication'

Reading around various blogs, it seems like a good idea to save the replication scripts when you first set it up. The easiest method to resolve the errors seen above, is to re-create the replication exactly and then run the “Disable Publishing and Distribution” wizard. But, if this isn’t possible, then the @ignore_distributor=1 option will help you clean up orphaned replication.

 

Advertisements

One thought on “Removing Replication

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s