none
SQL Server 2008 Merge Replication: Metadata Cleanup Failure

    Question

  • We are an issue, related to the metadata cleanup. Some time ago we reduced the retention period down from the default to 3 days and did not encounter issues, then last suddenly began to encounter the error below for various publications. It does not occur on every synchronisation. The output from setting agent parameters defined at http://msdn.microsoft.com/en-us/library/ms151872(d=printer,v=sql.100).aspx is shown below
    2013-04-24 11:09:13.648 The merge process could not perform retention-based metadata cleanup in database 'XXXXX'. If this failure continues, try increasing the query timeout for this process, or try reducing the retention period. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
    2013-04-24 11:09:13.648 OLE DB Subscriber 'XXXXXXX': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
    2013-04-24 11:09:13.664 Percent Complete: 57
    2013-04-24 11:09:13.664 The merge process could not perform retention-based metadata cleanup in database 'XXXXX'. If this failure continues, try increasing the query timeout for this process, or try reducing the retention period. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
    2013-04-24 11:09:13.664 OLE DB Distributor 'FULTON-LON1-C': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
    2013-04-24 11:09:13.742 Percent Complete: 0
    2013-04-24 11:09:13.742 Category:NULL
    Source:  Merge Replication Provider
    Number:  -2147199466

    Message: The merge process could not perform retention-based metadata cleanup in database 'XXXX'. If this failure continues, try increasing the query timeout for this process, or try reducing the retention period. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.

    Viewing the synchronisation history in replication monitor for the failed synchronisations shows the following:
    Error messages:
    The merge process could not perform retention-based metadata cleanup in database 'XXXX'. If this failure continues, try increasing the query timeout for this process, or try reducing the retention period. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199466)
    Violation of UNIQUE KEY constraint 'UQ__#oldgens__DCDFEE736CD828CA'. Cannot insert duplicate key in object 'dbo.#oldgens'. (Source: MSSQLServer, Error number: 2627)
    Get help: http://help/2627
    Thursday, April 25, 2013 8:57 AM

All replies

  • I determined that the error was caused by the system stored procedure that cleans the metadata: sp_mergemetadataretentioncleanup. Running it manually generated the errors that were showing in Replication Monitor.

    I managed to locate the source code for the procedure courtesy of Google and identified the cause of the failure: there was a duplicate generation number in MSMerge_genhistory and the generation number must be unique in the temporary table that is populated by the stored procedure. Having determined that this was the cause, I identified the offending article with duplicate generation numbers. 

    Looking at the time the generations were created I believe this was caused by a SQL Server mirror switch of the publisher database on the 19th. We didn’t start seeing the errors for until 23 because we have a 3 day retention period for the publications so the stored procedure would not have attempted to clear them up until then (it actually cleans up the data after the retention period plus a fixed period).

     To clear up the duplication I dropped the article from the publication which cleared up the metadata associated with it and then added it back in
    Thursday, April 25, 2013 3:01 PM
  • I would have disabled the metadata cleanup and uploaded the data to my publisher and then reinitialized.

    This should have gotten you through this problem. The switch to do this is -MetadataRetentionCleanup and set it to 0.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Thursday, April 25, 2013 3:03 PM
    Moderator
  • Thanks Hilary

    The challenge for us is this is a 7*24 hour system with multiple TB of data and our publisher and subscriber are on a comparatively slow VPN between two data centres 80 miles apart

    Thursday, April 25, 2013 3:20 PM
  • Are you using merge replication for HA or DR? If so it is a really bad choice. P2p or bi-directional transactional replication is a much better fit!

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Thursday, April 25, 2013 3:23 PM
    Moderator
  • We are. It's an architecture I inherited and the nature of our service is such that changing it now would be a major undertaking.
    Thursday, April 25, 2013 3:33 PM