April 25, 2013 at 3:00 am
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)
Get help: http://help/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
April 25, 2013 at 9:03 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply