The merge agent may fail during merge replication synchronization in SQL Server 2008 R2 or in SQL Server 2012

Article translations Article translations
Article ID: 2733630
Expand all | Collapse all

On This Page

Symptoms

During synchronization of merge replication in Microsoft SQL Server 2008 R2 or in Microsoft SQL Server 2012, 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 error message may occur for multiple reasons. These include many reasons that are expected under certain circumstances. To determine whether you have encountered this message 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 result:
select sentgen from SubscriberDB.dbo.sysMergeSubscriptions where pubid = subid and sentgen is not null


Collapse this imageExpand this image
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 generation
The output of the query should resemble the following:

Collapse this imageExpand this image
Query result by generation


You may encounter the error message 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.

Cumulative update information

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 SP1
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 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

Properties

Article ID: 2733630 - Last Review: November 21, 2012 - Revision: 4.1
Keywords: 
kbtshoot kbexpertiseinter kbprb kbsurveynew KB2733630

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com