Select the product you need help with

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

Article ID: 2733630

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.

Back to the top | Give Feedback

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

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

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.

Back to the top | Give Feedback

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

(http://support.microsoft.com/kb/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

(http://support.microsoft.com/kb/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

(http://support.microsoft.com/kb/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

(http://support.microsoft.com/kb/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

(http://support.microsoft.com/kb/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

(http://support.microsoft.com/kb/2567616/ )

The SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 SP1 was released

Back to the top | Give Feedback