1

I just joined my new office as database administrator. Here we are using SQL Server merge replication. It surprises me that 3 of major replication tables

  1. Msmergre_contents
  2. Msmergre_genhistory
  3. Msmergre_tombstone

Size of Msmergre_contents grew up to 64GB & no of records about to 64 billion and this is happening due to None set as the Expiration Period for subscriptions.

Now I want to clean up this table. As we are using a Simple recovery model, when I wrote a delete query on this table, everything got stuck. I have no downtime to stop/pause replication process.

Can anyone help me out how to minimize its size or delete half of its data?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ahsan Mumtaz Abbasi
  • 53
  • 1
  • 11

1 Answers1

2

You should not be directly deleting from the Merge system tables, that is not supported.

Instead, the proper way to cleanup the metadata from the Merge system tables is to set your Subscription expiration to something other than None, the default is 14 days. Metadata cleanup will be run when the Merge Agent runs, it executes sp_mergemetadataretentioncleanup. More information on subscription expiration and metadata cleanup can be found in How Merge Replication Manages Subscription Expiration and Metadata Cleanup.

However, since you most likely have a lot of metadata that needs to be cleaned up, I would gradually reduce the retention period. An explanation of this approach can be found here:

https://blogs.technet.microsoft.com/claudia_silva/2009/06/22/replication-infinite-retention-period-causing-performance-issues/

Hope this helps.

Brandon Williams
  • 3,695
  • 16
  • 19
  • Thank you so much for your suggestion but i have a little doubt. that when i will set expiration of subscription of any date its mean it will delete the records and when ever it will perform deletion command on such huge table definitely it will stuck the whole replicational process as we are using "simple" recovery model . – Ahsan Mumtaz Abbasi Mar 30 '18 at 17:40
  • That's why it needs to be done gradually like the approach described in the article. Also, the recovery model has nothing to do metadata cleanup. – Brandon Williams Mar 30 '18 at 17:51
  • Thank you so much sir, u really solve my problem .. thanks this article really help me to find or make a data set which can be deleted from mentioned tables. – Ahsan Mumtaz Abbasi Mar 30 '18 at 18:06