You are currently offline, waiting for your internet to reconnect

The merge agent may fail during merge replication synchronization in SQL Server

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 generation
The 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 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: 10/22/2015 19:39:00 - Revision: 5.0

  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Express
  • Microsoft SQL Server 2008 R2 Datacenter
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Express
  • Microsoft SQL Server 2008 R2 Parallel Data Warehouse
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 R2 Web
  • Microsoft SQL Server 2008 R2 Workgroup
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Web
  • Microsoft SQL Server 2008 Workgroup
  • Microsoft SQL Server 2012 Developer
  • Microsoft SQL Server 2012 Enterprise
  • Microsoft SQL Server 2012 Enterprise Core
  • Microsoft SQL Server 2012 Express
  • Microsoft SQL Server 2012 Standard
  • Microsoft SQL Server 2012 Web
  • Microsoft SQL Server 2014 Developer
  • Microsoft SQL Server 2014 Enterprise
  • Microsoft SQL Server 2014 Enterprise Core
  • Microsoft SQL Server 2014 Express
  • Microsoft SQL Server 2014 Standard
  • Microsoft SQL Server 2014 Web
  • kbtshoot kbexpertiseinter kbprb kbsurveynew KB2733630
Feedback