March 25, 2010 at 11:46 am
So the previous admin had infinite retention set and msmerge_contents now contains 7 years worth of transactions.
My current approach has been to decrease retention by 10 days at a time which is taking about 30 minutes per run just to do the cleanup.
Considering some records are 2500 days old, this will take forever before I get down to a reasonable number. Increasing the batch size seems to increase the time it takes to complete by more than a 1-1 ratio (i.e. 20 days takes 75 minutes).
Am I stuck, or is there a better way to do this?
March 29, 2010 at 6:01 am
Just FYI in case anyone else comes across a similar problem. In this scenario, drastic performance improvement was obtained by defragmenting the index on msmerge_contents. Due to log space issues, I wasn't able to reindex, but I was able to do the folllowing:
dbcc indexdefrag (databasename, msmerge_contents, uc1syccontents)
This took about 12 hours to complete against msmerge_contents with just under 40 million records.
July 28, 2010 at 3:25 pm
Hi there, I know this do not help you out on your question, but how are you setting the retention for the MSMerge_Contents table? I am trying to find out myself for this settings for an issue I am looking to investigate.
July 29, 2010 at 11:29 am
retention period was initially set to infinate- which was definitely a contributor to the problem. I've been gradually decreasing the retention period over time.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply