We are using Merge replication of SQL Server 2005 SP4 on Windows Server 2003, and I've set retention period to 14 days. I do not check the size of MSMerge_Contents, until recently the merge performance was slower than before. After checking the row number
of MSMerge_Contents, I then realized the number is more than 5 million! Is this the root cause of performance downgrade? How to check if the regular cleanup has been carried out always?
I then executed only "exec sp_mergemetadataretentioncleanup" on the replication database on Publisher. After running more than half an hour, I saw there were a lot of downloads and uploads on replication monitor, abount tens of thousands! Usually
the number of each run (I've set to merge data per 5 mins) is only hundreds or about a thousand.
Then, I decided to stop executing the manual cleanup and checked the row number of MSMerge_Contents, and it dropped to about 3 million. However, the replication downloads and uploads still grew, and reached hundreds of thousands!
After it all finished making so much downloads/uploads, the row number of MSmerge_Contents was still 5 million, a little less than before.
This is beyond my knowledge, and any help will be greatly appreciated.
View Complete Post