Symptoms
During synchronization of merge replication in Microsoft SQL Server 2008 and later versions of SQL Server, the merge agent may fail. Additionally, you receive the following 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). (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199401)Get help: http://help/MSSQL_REPL-2147199401
This issue may occur for multiple reasons. These include many reasons that are expected under certain circumstances. To determine whether you have encountered this error unexpectedly, examine the metadata details that are described in the "Cause" section.
Cause
The cause of this problem can be found in the following system tables on the subscriber:
-
dbo.sysMergeSubscriptions
-
dbo.MsMerge_Genhistory
Make sure that the merge agent is no longer synchronizing. Then, run the following query, and examine the results:
select sentgen from SubscriberDB.dbo.sysMergeSubscriptions where pubid = subid and sentgen is not null 
select top 20 * from SubscriberDB.dbo.MSmerge_genhistory where generation >= sentgen_value order by generationThe output of the query should resemble the following:
-
The value in the generation column in the first row is slightly greater than the sentgen value in the previous query.
-
The value in the genstatus column is 4.
-
The value in the changecount column is 0.
Note The value of the coldate column of the first row is also typically much older than the value of that column in the other rows.
Resolution
To resolve this problem, reinitialize the subscriber that produced the error.In some scenarios, you may be able to resolve this problem without a complete reinitialization. Contact Microsoft Customer Support Services for information about a possible workaround.To prevent future occurrences of this problem, apply one of the following fixes, depending on your version of SQL Server.
Cumulative update information
SQL Server 2008
There is no fix for this issue in SQL Server 2008.
SQL Server 2008 R2 Service Pack 2
The fix for this issue was first released in Cumulative Update 3. For more information about how to obtain this cumulative update package for SQL Server 2008 R2 Service Pack 2, click the following article number to view the article in the Microsoft Knowledge Base:
2754552 Cumulative update package 3 for SQL Server 2008 R2 Service Pack 2 Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 R2 Service Pack 2 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
2730301 The SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 Service Pack 2 was released
SQL Server 2012
The fix for this issue was first released in Cumulative Update 4. For more information about how to obtain this cumulative update package for SQL Server 2012, click the following article number to view the article in the Microsoft Knowledge Base:
2758687 Cumulative update package 4 for SQL Server 2012 Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2012 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
2692828 The SQL Server 2012 builds that were released after SQL Server 2012 was released
SQL Server 2008 R2 Service Pack 1
The fix for this issue was first released in Cumulative Update 8. For more information about how to obtain this cumulative update package for SQL Server 2008 R2 Service Pack 1, click the following article number to view the article in the Microsoft Knowledge Base:
2723743 Cumulative update package 8 for SQL Server 2008 R2 SP1Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 R2 Service Pack 1 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
2567616 The SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 SP1 was released
