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
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?
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:
Hope this helps.