Removing Merge Metadata
-
We've finally updated to SQL 2000, yeahhhhh.
While on SQL 7.0 I used Mike Hoteks solution for cleaning up merge metadata.
Before I set this up again on my 2K machines I wanted to know how others handled their merge metadata, and if there was different way to do this (perhaps Microsoft Supported)
Any comments or suggestions are greatly appreciated.
Thanks,
Barbara
-
We use the sp_mergecleanupmetadata that is automatically called if you use the default merge profile in SP3. This assumes all subscribers and publishers are SQL 2K with SP3. It's a long running process so I disable the auto execution and run it on my own schedule.
One thing I am sure to do is a update statistics with full scan on all the MSMerge* tables at least once a day. I don't know all of the rules in the auto update stats engine, but when the table is large I know it can not be trusted.
-
Search in Books Online for "Merge Meta Data Cleanup". This will give you some decent background on the different options that are available. I've been involved in the past in an environment that used merge replication pretty heavily, and the defaults worked well for us (all servers were SQL 2K sp1 and later).
We did reduce the retention period from its default (14 days) to much less, in order to keep the merge tables as small as possible.
-
Thank you both for your help
I had a few questions about both of your comments.
I am running SQL2K with sp3.
What job automatically calles sp_mergecleanupmetadata?
I did go to BOL but I read the following about using sp_mergecleanupmetadata:
If you want to run sp_mergecleanupmetadata without the subscriptions being marked for reinitialization:
- Stop all updates to the publication and subscription databases.
Unfortunately, I cannot stop all updates as our servers need to be available 24/7. Do you stop all updates before you run it?
Thanks again, I really appreciate the help,
Barbara
-
Setup a test merge publication, using a Microsoft-supplied profile, being sure that the subscriber and publisher are at sp1 or higher. Put a trace on the server using Profiler. Watch the merge agent do its work. You'll find that it automatically calls sp_mergemetadataretentioncleanup. You can see it get called on both the publisher and the subscriber.
If you have an indefinite retention period, the auto-cleanup won't actually clean anything up. Also, if your profile has specified not to cleanup, sp_mergemetadataretentioncleanup won't be called--but you can run it manually. I haven't experimented with this so can't offer any advice.
I think the older sp_mergemetadatacleanup is for use if your topology includes servers running something prior to SP1. Otherwise you don't need to mess with it.
-
Thank you again Glen for your help. I plan on setting up the test merge environment to test some of this out.
I should have mentioned in my original email that I am running Merge replication.
I noticed that the job named: "Distribution clean up: distribution" job is disabled, by default.
I looked at BOL "Miscellaneous Agents" and it appears that job only runs for snapshot and transactional publications. Do you know is is supposed to run for Merge Replication as well?
Is there a scheduled job that does the auto-cleanup, mentioned above?
When I check my Merge Agent Profile -MetadataRetentionCleanup value is 1.
Thanks again,
Barbara
-
>>I noticed that the job named: "Distribution clean up: distribution" job is disabled, by default.
I looked at BOL "Miscellaneous Agents" and it appears that job only runs for snapshot and transactional publications. Do you know is is supposed to run for Merge Replication as well?<<
It removes HISTORY information for all three
>>Is there a scheduled job that does the auto-cleanup, mentioned above?<<
You can set up one with sp_mergemetadataretentioncleanup as Glen suggested. This wil cleanup the METADATA from MSmerge_tombstone, MSmerge_genhistory and MSmerge_contents. Definetly a profiler trace is a very good place to start because as you already discovered -MetadataRetentionCleanup is ENABLED
hth
* Noel -
We did not have to set up a special job to call sp_mergemetadataretentioncleanup; it ran automatically. However, our merge agents are configured to stop and restart frequently. I think it's possible that the cleanup only occurs when the agent stops, or maybe when it starts. Sorry, I don't have time to test that right now.
If your merge agent is running continuously, and the cleanup happens only on agent stop or start, then maybe you could set up a special job to call it as has been suggested.
-
Barbara, you found the correct profile option (-MetadataRetentionCleanup = 1). If your researching metadata cleanup, I have to assume you are concerned about performance. I'm in an environment with several hundered subscribers, and this clean-up call is very expensive to run every time the sync occurs. I setup my own controls so I can either turn this option on and off using the profile, or leave it off all together and call it on my own schedule.
As the other posts suggested, you don't have to do anything special for the agent to perform the clean-up. I just suggest that you look at these calls in profiler and see how resource intensive they become in your environment.
-
Thank you all very much for your time and your responses.
This site is great for sharing knowledge and I don't know what I would do without it.
Thank you all again!
Barbara
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply