July 19, 2007 at 9:47 pm
Hi,
I have this issue with a SQL Server 2005 Standard edition SP1 replication and would really appreciate anybody who can help.http://www.sqlservercentral.com/forums/images/emotions/blink.gif
Merge replication is setup with three subscribers. The problem i'm facing is only with one of the subscribers. Until a couple of days ago I noticed it was set for infinite retention and therefore enabled the Meta data retention clean up in the publication for 7 days.
After that I continually tried stopping and starting the merge agent but am not seeing any reduction in the size of the metadata. The normal size of the data file is around 4 GB but with the metadata it is around 11GB and growing.
I think this is also affecting the replication performance because I continually get the error message
"THE REPLICATION AGENT HAS NOT LOGGED A PROGRESS MESSAGE IN 10 MINUTES. THIS MIGHT INDICATE AN UNRESPONSIVE AGENT OR HIGH SYSTEM ACTIVITY. VERIFY THAT RECORDS ARE BEING REPLICATED TO THE DESTINATION AND THAT CONNECTIONS TO THE SUBSCRIBER, PUBLISHER, AND DISTRIBUTOR ARE STILL ACTIVE."
I checked using dbcc opentran and dbcc inputbuffer to see what was the longest running transaction and got the result "SYS.SP_MSADD_MERGE_HISTORY90;1". I have searched online to see what this sproc is doing and it doesn't seem to be doing any deletion only addition to the metadata. The only sproc I found to clean up the merge meta data is "SP_MERGEMETADATARETENTIONCLEANUP". However this doesn't seem to be deleting the metadata even though the metadata retention is enabled.
Another issue is since "SYS.SP_MSADD_MERGE_HISTORY90;1" is running almost continuously I cannot even get a simple row count for the merge meta data. The log file keeps growing and reaches about 30GB in one day. I keep having to shrink the log file to free up space in the server.
Could all this be due to the fact that we are running SP1 ? I noticed that there's a cumulative hotfix package available for SP2 as well !! This is really creating a lot of headaches to our team after our recent SQLServer 2005 migration from SQLServer2000. Everything was working fine in SQLServer 2000 and we have yet to see the promised bug free and improved performance in SQLServer 2005.http://www.sqlservercentral.com/forums/images/emotions/crazy.gif
Thanks in advance,
Purvis
July 20, 2007 at 10:25 am
Clearly setting up a 'test' for identifying that SP2 would fix a 'bug', rather than there being something wrong in your logic, is not really feasible. If you revert to infinite retention, does the error message go away? Also, does the cleanup proc you referenced (SP_MERGEMETADATARETENTIONCLEANUP) take parameters? If so, you've not mentioned what you specified. It's also possible there's another setting. My environment does not use replication, so I'm speculating here...
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply
