"The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Subscriber for changes not yet sent to the Publisher. (MSSQL_REPL-2147199402)" - Error reappearing in SQL Server 2008 SP1

Question

I have replication setup between a Publisher and a Subcriber with a nominal production load on the system. The replication between the servers has been running successfully for few days. However, after 5-6 days, the replication between the servers stopped with the below error message:

"The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Subscriber for changes not yet sent to the Publisher. You must reinitialize the subscription (without upload)."

When i browsed through the MS forums, I found that the issue was observed in SQL Server 2005 and has been fixed as part of the hotfix available under the link below:

http://support.microsoft.com/kb/919929

I am currently using SQL Server 2008 SP1 Standard Edition and the same issue is reproducible in it now.

Hence, can anyone please let me know if the above hotfix (of SQL Server 2005) is available for the SQL Server 2008 SP1 Standard Edition version also? Please help.

Thanks in advance.

Answer 1

This is a long standing bug which MS has not been able to squish. Even the hot fix did not solve all occurrences of it. Go to the latest SP for SQL 2008.

Basically the merge agent goes to clean up metadata on the subscriber which is already gone.

You may be able to get around it by setting -MetadataRetentionCleanup to 0 on your merge agent binary. This may buy you time to do the upload and then reinitailize.

---

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

Answer 2

Hi,

You can try the below query , but safe side take full backup of the subscriber DB..

--- This query should be run at Subscriber window.

use <DBNAME>  
update sysmergesubscriptions set cleanedup_unsent_changes = 0 where cleanedup_unsent_changes = 1 and subscriber_server = '<Publisher>' 

After this query try to sync your subscriber DB.

---

Regards JakDBA Please Mark this as Answered if this solves your issue or satisfied with the response.

Answer 3

Hi All,

Thanks for your replies. Looks like only the latest service pack of SQL Server 2008 may solve this problem.

Meanwhile, trying out the below SQL Query is the possible workaround.

update sysmergesubscriptions set cleanedup_unsent_changes = 0 where cleanedup_unsent_changes = 1 and subscriber_server = '<Publisher>'

Answer 4

Thank you for your post! It works like a chame!