Post reply

Cleanup of Merge Metadata....again

  • April 12, 2005 at 12:08 pm

    #165075

    OK, I’m still confused; please respond to me like I’m an idiot because apparently that's what I am.

     

    I am running SQL2K with sp3, continuous merge replication, only one subscriber.

     

    My current setting for Subscription expiration is the default of 7, I have just recently changed this from the default of 14 days, as our Subscriber is basically always available.

     

    I am still trying to figure out how to cleanup my merge metadata.  From what I have read and from what I understand from my previous post sp_mergemetadataretentioncleanup is automatically run, if the agent running Merge has the Parameter for –MetadataRententionCleanup set to 1

     

    I am under the assumption that if I have has the Parameter for –MetadataRententionCleanup set to 1

    it should automatically be cleaning up my metadata.    Even though I have it set to 1 it does not appear like it is actually cleaning out MSmerge_contents or MSmerge_tombstone.  These tables just keep growing. 

     

    Now if I had this parameter set to 0 then I would need to use sp_mergecleanupmetadata to clean up MSmerge_contents or MSmerge_tombstone.

     

    Would there ever be a reason for running both?

     

    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:

    1. Stop all updates to the publication and subscription databases.

    Unfortunately, I cannot stop all updates as our servers need to be available 24/7.

    Does anyone run this without stopping replication first? And if you do run it do you have –MetadataRententionCleanup set to 0?

    If anyone can help I truly would appreciate it.

    Thanks,

    Barbara

  • April 12, 2005 at 3:07 pm

    #552251

    Barbara,

    I believe that where you are  confused is on the sp. There are two sp.

    1) sp_mergecleanupmetadata

    2) sp_mergemetadataretentioncleanup

    The First should be used for topologies with SQL Server 2000 SP1 or less (which is not your case!)

    The Second one is the one to control the process manually for SQL Server > SP1 which is your case and when the MetadataretentionCleanup is set to 0 (not automatic)

    There is also a catch though the second sp is executed automatically by merge Agent at at startup! when MetadataretentionCleanup is set to 1 therefore when in continuous mode it will be called only once!

    hth

     


    * Noel

  • April 13, 2005 at 7:17 am

    #552403

    Noel, thank you very much for the clarification.

    So how would one go about cleaning up merge metadata if they are running in a continuous mode? 

    Thanks again,

    Barbara

  • April 13, 2005 at 8:07 am

    #552415

    I just checked my production merge tables and it appears that records are being removed from MSmerge_contents, MSmerge_genhistory, and MSmerge_tombstone.

    As I mentioned in my first posting, I recently changed the setting for Subscription expiration to 7, from the default of 14 days. 

    I checked the merge agent’s history and last night we received an error message that "The process could not query row metadata at the 'Subscriber'."

    I have my merge job setup to restart itself so I'm guessing that when it did, it must have executed the sp automatically by the merge Agent at startup, as Noel had mentioned.

    So now I'm wondering if I should just leave things they way they are and monitor the merge table sizes and if they start to grow out of control just stop and restart the agent.  Is this a good idea or does anyone have a better suggestion? 

    Thanks,

    Barbara

     

  • April 14, 2005 at 8:36 am

    #552721

    Barbara,

     

    I am sorry couldn't get back to you sooner.

    What you can do is to schedule a job with sp_mergemetadataretentioncleanup if you need to run it continuous mode.

     

     


    * Noel

  • April 14, 2005 at 9:11 am

    #552741

    Ohhh no worries Noel.  I appreciate your help.

    I'll keep my eye on it and if the tables seem to start getting to big I'll run that sp_mergemetadataretentioncleanup stored procedure.

    Thank you again for your help,

    Barbara

     

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply