Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

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

Query results

The sentgen column marks the oldest group of changes that the subscriber sent to the publisher. If multiple values are returned, examine the other columns in the dbo.sysMergeSubscriptions table to make sure that you are viewing the metadata for the correct publication. Next, use the value that this query returns in the following query:

select top 20 * from SubscriberDB.dbo.MSmerge_genhistory where generation >= sentgen_value order by generationThe output of the query should resemble the following:

Query result by generation

You may encounter the error unexpectedly when all the following conditions are true:

  • 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

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×