question

nervousdba avatar image
nervousdba asked

major help! cleaning up merge replication with infinite retention

Hi Team,

At the request of a user, I added a relatively small table to merge replication. I expected this would have minimal impact. I was really really wrong.

I did not realize that the replication is set to infinite retention and therefore has almost 40 million records in msmerge_contents. When a merge replicaiton happens it gets to one table, then takes ages waiting on sp_enumchanges (I already increased timeout and it doesnt timeout anymore).

I am tempted to start cleaning up msmerge_contents by gradually decreasing the rention period (from 2500 days), but when I do it one day at a time it takes 40 minutes to clean up 25000 records or so... that would mean a really really long time to get down to a reasonable size.

Is there another alternative? What would happen if I truncate the msmerge_contents, msmerge_genhistory and then reinitialized the subscription?

Thanks in advance for your help.

NervousDBA

replicationmerge
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

btw, this is 2000 sp3
0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.