What is the proper way to completely clean Merge Replication setup ?

I have tried to delete the subscriptions, then delete the publication then "Disable Publishing & Distribution" & delete the distribution database then started setting up the replication again.

unfortunately, after that I discovered that dbo.msmergesubscriptions table still have old records about old subscriptions I used to have before 2 years !!

Is there any official documentation about how to properly & totally clean Merge Replication setup?


Merge replication is a bit tricky to remove as compared to Transactional replication.

Below are the steps that I follow successfully to remove obsolete/leftover merge replication bits and pieces :

  1. Backup the merge replicated database(s) at the publisher and subscriber(s) before proceeding.
  2. Script out merge replication from the publisher server and store the create/delete scripts safely in another location.
  3. Obtain the list of published articles and its existing triggers for reference in later step by running the query below on the publisher and subscriber’s merge replicated database and storing the result somewhere for the time being (ie. In a text file)

    set nocount on 
    print 'get the list of published articles and its triggers' 
    p.name as publication_name, 
    o2.name as tbl_name, 
    o1.parent_obj , 
    o1.name as trigger_name, 
    left(o1.name, 3) as trigger_type, 
    o1.crdate as create_date 
    into #t 
    from sysobjects o1 
    join sysobjects o2 on o1.parent_obj = o2.id 
    join sysmergearticles a on o2.id = a.objid 
    join sysmergepublications p on a.pubid = p.pubid 
    where o1.xtype = 'TR' 
    and p.name = '' 
    order by p.name, o2.name, o1.name 
    select * from #t 
    print 'generate the drop statements for the replication triggers' 
    select 'drop trigger ['+trigger_name+']' 
    from #t 
    where trigger_type in ('ins','upd','del') 
    drop table #t 
  4. Drop the merge replication by running the delete script generated in step 1 from the publisher.

  5. On both the publisher and subscriber(s), clean out any triggers that are not removed by the replication delete script by running the drop trigger script generated in step 3.

  6. On both the publisher and subscriber(s), check the sysmergepublications, sysmergesubscriptions, and sysmergearticles tables whether the entries for the merge replication still exists. If so, run the following script to remove the entries from sysmergearticles and sysmergepublications

    Note : You may also delete any entries in sysmergearticles that are no longer referenced by entries in sysmergepublictaions/sysmergesubscriptions, but please be very careful when doing this

       -- delete the obsolete entries from sysmergearticles on the publisher side 
       delete from sysmergearticles 
       where pubid in ( select p.pubid 
       from sysmergepublications p 
       where p.name = '' 
       -- delete the obsolete entries from sysmergearticles on the subscriber side 
       delete from sysmergearticles 
       where pubid in ( select p.pubid 
       from sysmergesubscriptions p 
       where p.publication = '' 
       -- delete the obsolete entries from sysmergepublications 
       delete from sysmergepublications 
       where name = '' 
        -- delete the obsolete entries from sysmergesubscriptions 
        delete from sysmergesubscriptions 
        where publication = ''
  7. If there’s no other merge replication set up on this database you may also truncate the MSmerge* tables on the publisher/subscriber databases.

Following above steps will ensure that MERGE Replication is cleanly removed - no trace left behind !

Note: I have been using above method in my current environment without any issues. But please test this before hand ... as it goes without saying.. if something is screwed up, don't blame this site and me :-)

| improve this answer | |
  • 1
    I was trying your script in step 3, but it seems that trigger names in my case starts with MSmerge_ instead of ('ins' or 'upd' or 'del' ) like you presume, which is causing the list of drop trigger script to be empty, Is it expected to be different between systems ? – Adel Khayata Jun 19 '13 at 6:22
  • 1
    Also you are using a filter in the query ( and p.name = '') which is trying to select a publication which has no name. – Adel Khayata Jun 19 '13 at 6:22
  • 2
    @AdelKhayata you have to put in the publication Name. – Kin Shah Jun 19 '13 at 14:07
  • This is not how you drop a Merge topology. – Brandon Williams Jun 24 '13 at 15:26
  • @BrandonWilliams The question is not how to drop merge replicaiton, it is how to clean up Merge replication after it is not dropped correctly. – Kin Shah Jun 24 '13 at 15:39

Somehow you are ending up with orphaned replication metadata after deleting the subscriptions and/or the publication. This can happen when using the GUI to drop subscriptions and publication. Instead, try using T-SQL replication stored procedures to delete the subscriptions and publication.

How to: Delete a Push Subscription (Replication Transact-SQL Programming)

How to: Delete a Pull Subscription (Replication Transact-SQL Programming)

How to: Delete a Publication (Replication Transact-SQL Programming)

After deleting the subscriptions and publication, verify the replication metadata was removed. If not, use sp_removedbreplication to remove all replication objects from the databases.

| improve this answer | |
  • 1
    Yes, You are right, I used GUI to drop subscriptions and publication and also disable publishing & distribution, but why using The GUI is different than using T-SQL commands ? isn't it at the end the same commands executed in the background ? – Adel Khayata Jun 19 '13 at 6:00
  • 1
    Also the other answer in step 5 is referring to run a script to delete left behind triggers even after dropping everything using T-SQL commands!! so this means even if I use T-SQL commands, I still might have some replication meta data left in the setup!! Is this really possible ? Is there any Official Microsoft document to clarify how this should be done ? – Adel Khayata Jun 19 '13 at 6:06
  • 2
    I have provided the documentation on how to remove subscriptions and publications.Yes, you are correct, the GUI uses the same T-SQL commands to delete subscriptions and publications, however, notice that when removing a pull subscription that you must execute a command at the subscriber and then execute a command at the publisher. When using the GUI, depending on where you initiate the delete, the publisher or subscriber may not be available and a command may never be executed, thus leaving orphaned replication metadata. It is best to use T-SQL replication stored procs to remove replication. – Brandon Williams Jun 19 '13 at 15:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.