I have more than one merge publication configured on a SQL Server 2005 SP1 server. One of the publications replicates a couple stored procedures. All subscriptions fail occassionally. The use case is remarkably similar to .
Every other Sunday (yeah, I know, Sunday... Murphy's Law strikes again...) replication on the stored procedures publication fails with the following error:
The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Publisher for changes not yet sent to the Subscriber. You must reinitialize the subscription (without upload). (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199402)
Screenshot:
The identifies this as a bug to be addressed in SP2 and prescribed a workaround:
1. Right-click the publication and click Properties:
The "Subscription expiration" defines the retention interval discussed in the . Set it to a high number of days (or weeks, months, or years) to avoid this issue until SP2 is released:
Click OK to continue. Right-click the subscription again, and this time click "Reinitialize All Subscriptions":
The Reinitialize Subscription(s) dialog displays. Uncheck the "Upload unsynchronized changes before reinitialization" checkbox. Select the "Use a new snapshot" option and check the "Generate the new snapshot now" checkbox:
Note: "Reinitialize All Subscriptions" only reinitializes subscriptions to the selected (right-clicked) publication. It does not reinitialize all subscriptions to all publications.
Click the "Mark For Reinitialization" button. This should take care of it until SP2 is released!
The downside: you have to monitor your subscriptions to see when they haven't synchronized within the desired interval. Replication Monitor is a great utility for this and you can sort on the Last Synchronization column to check the last synch interval. To start Replication Monitor, right-click the Replication node in Object Explorer and click Replication Monitor:
I profiled Replication Monitor, snagged the SQL Replication Monitor executed, and created a couple custom Reporting Services reports to help me keep an eye on the situation. The stored procedure I started with was:
exec [distribution].sys.sp_replmonitorhelpsubscription @publisher = N<server_name>, @publication_type = 2, @mode = 0, @exclude_anonymous = 0, @refreshpolicy = N'0'
:{> Andy
Technorati Tags: SQL Server Merge Replication SP2 Merge Agent failed -2147199402