Sql-server – How to properly troubleshoot Merge Replication False Conflicts

"It looks like something is updating the same row at both servers with different content and made merge agent crash?" This is handled by merge conflict tables, and would not cause the issues you are describing. These conflict tables are located on the publisher database, and are named like: MSMerge_conflict__.

To answer your question about what reinitialization does, by default, reinitialization will take a snapshot of your published articles, drop the articles on the subscriber side, recreate the articles on the subscriber side, and then bulk load data from the snapshot into the subscriber articles. Since this is a production environment, and those articles need to be available on the subscriber side, this should only be used as a last resort.

What you can do is query the MSrepl_errors table on the Distribution database. This will provide you with a command_id and an xact_seqno. You can use these values as inputs into the sys.sp_browsereplcmds stored procedure. This will provide you with command text that is actually failing. Using this information, you can better understand the nature of the failure. If a particular row cannot be inserted or deleted at the subscriber, you may have to either delete the existing row (to allow the insert) or insert a dummy row (to allow the delete), respectively.

I hope this information helps,

Matt

Straight from Advanced Merge Replication Conflict Detection and Resolution:

The Conflict Viewer displays information from three system tables:

• Replication creates a conflict table for each table in a merge article, with a name in the form MSmerge_conflict__.

• Conflict tables have the same structure as the tables on which they are based. A row in one of these tables consists of the losing version of a conflict row (the winning version of the row is in the actual user table).

• The MSmerge_conflicts_info table provides information about each conflict, including the conflict type.

• The sysmergearticles table identifies which user tables have conflict tables and provides information about the conflict tables.

This means that if you find a record in the MSmerge_conflict_[PublicationName]_[ArticleName] table, it is the "loser". The record that you find in the actual table is the "winner".

Hope this helps,

Matt