Removing a Merge Replication Publication

Merge replication in any SQL Server edition is notoriously bad tempered. It does seem to be getting more stable and "intelligent" as time goes on but problems still rear their ugly heads on occasion.

One big issue lots of people encounter is removing a publication. All too often I've attempted to delete subscriptions and publications, either using SSMS or via tsql, and loads of metadata and related merge database objects are left hanging around. This may be fine, albeit a little untidy, if you're just removing replication because you don't want it any more. But there are many cases, especially with older SQL Server editions, where you need to remove replication to perform a feature deployment or other maintenance. It is then necessary to rebuild the publication(s) soon afterwards.

It is in such cases that these objects and data that are left lying about have the potential to cause misery. Orphaned subscriptions, for example, can cause unique constraint errors when metadata is copied between servers. Historic data left in the MSmerge tables can cause subscriptions to take ages to drop. Or even cause them to fail*. I've spent way too much time battling niggly replication problems that were caused by some old data sitting in a merge table.

As an aid I've written the following script to help clear down any data or objects when removing a merge publication.

/*DELETE metadata scripts below*/
-- run these to make dropping the subscriptions quicker
delete from MSmerge_contents
where generation in (select h.generation from sysmergepublications as p
join sysmergearticles as a on a.pubid = p.pubid
join MSmerge_genhistory as h on a.nickname = h.art_nick
where p.name = @pubname)

delete from MSmerge_tombstone
where generation in (select h.generation from sysmergepublications as p
join sysmergearticles as a on a.pubid = p.pubid
join MSmerge_genhistory as h on a.nickname = h.art_nick
where p.name = @pubname)

delete from MSmerge_genhistory
where art_nick in (select a.nickname from sysmergepublications as p
join sysmergearticles as a on a.pubid = p.pubid
where p.name = @pubname)
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--Then try this:
exec sp_dropsubscription @publication= 'MyMergePublication', @subscriber= 'MyMergeSubscriber'
exec sp_droppublication @publication= 'MyMergePublication'

--Then run this:
select * from sysmergeextendedarticlesview where pubid in (select pubid from sysmergepublications where name = 'MyMergePublication')
--If the above query returns results run the next query:
--SELECT pubid,name from sysmergepublications
DECLARE @pubname varchar(200)
SET @pubname = 'InvictusLocal(Merge)' --Enter name of desired publication
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
--Each column creates a drop script for a different object type
select 'IF EXISTS (SELECT 1 from sys.tables where name = ''' + conflict_table + ''') BEGIN DROP TABLE [' + conflict_table + '] END' as DropConflictTable
,'IF EXISTS (SELECT 1 from sys.objects where name = ''' + ins_conflict_proc + ''') BEGIN DROP PROCEDURE [' + ins_conflict_proc + '] END' as Drop_ins_conflict_proc
,'IF EXISTS (SELECT 1 from sys.objects where name = ''' + update_proc + ''') BEGIN DROP PROCEDURE [' + update_proc + '] END' as Drop_update_proc
,'IF EXISTS (SELECT 1 from sys.objects where name = ''' + insert_proc  + ''') BEGIN DROP PROCEDURE [' + insert_proc + '] END' as Drop_insert_proc
,'IF EXISTS (SELECT 1 from sys.objects where name = ''' + select_proc  + ''') BEGIN DROP PROCEDURE [' + select_proc + '] END' as Drop_select_proc
,'IF EXISTS (SELECT 1 from sys.objects where name = ''' + delete_proc  + ''') BEGIN DROP PROCEDURE [' + delete_proc + '] END' as Drop_delete_proc
,'IF EXISTS (SELECT 1 from sys.objects where name = ''' +  view_sel_proc   + ''') BEGIN DROP PROCEDURE [' +  view_sel_proc + '] END' as Drop_view_sel_proc
,'IF EXISTS (SELECT 1 from sys.objects where name = ''' +  metadata_select_proc   + ''') BEGIN DROP PROCEDURE [' +  metadata_select_proc + '] END' Drop_metadata_select_proc
,(SELECT 'DROP TRIGGER [' + o.name + ']' FROM sysobjects o (NOLOCK) join sys.tables as t on o.parent_obj = t.object_id
WHERE o.type = 'TR' AND o.name LIKE 'MSmerge_ins%'
and t.name = sysmergeextendedarticlesview.name) as Drop_Insert_Trigger
,(SELECT 'DROP TRIGGER [' + o.name + ']' FROM sysobjects o (NOLOCK) join sys.tables as t on o.parent_obj = t.object_id
WHERE o.type = 'TR' AND o.name LIKE 'MSmerge_upd%'
and t.name = sysmergeextendedarticlesview.name) as Drop_Update_Trigger
,(SELECT 'DROP TRIGGER [' + o.name + ']' FROM sysobjects o (NOLOCK) join sys.tables as t on o.parent_obj = t.object_id
WHERE o.type = 'TR' AND o.name LIKE 'MSmerge_del%'
and t.name = sysmergeextendedarticlesview.name)as Drop_Delete_Trigger
from sysmergeextendedarticlesview
where pubid  in (select pubid from sysmergepublications where name = @pubname)
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
/*DELETE articles, subscriptions and publication scripts below */
delete from sysmergearticles
where pubid  in (select pubid from sysmergepublications where name = @pubname)
delete from sysmergesubscriptions
where pubid in (select pubid from sysmergepublications where name = @pubname)
delete from sysmergepublications where name = @pubname
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------

I hope this helps you if you've run into problems.

*On a related note, recently I was working on a release for SQL Server 2005. We were hoping not to need to remove publications completely for the deployment. It involved adding a few new tables to an existing publication and some columns to an existing article in the publication. As it turned out, probably due to the several republishers in the topology we needed to drop all publications to make the changes.
Now, all excuses aside, the Distribution clean up: distribution job had not run successfully for many moons. As a result the MSmerge_history table on the distribution database had over 150 million rows. As a result, though my colleague and I were blissfully unaware of why, the drop subscription procedure was filling up the transaction log and failing. Constantly.
We finally tracked down the reason for these failures and spent the next 10 hours deleting the data from this table! That was our time plan thrown straight out the window :(
So a note to all out there (but especially to myself) - keep your eye on that job!