Clearing Resolved Merge Replication Conflicts

Share this Post

Content provided by: Steve Dybing, Microsoft SQL Server Support

Background on Merge Conflicts

When a Publisher and a Subscriber are connected and synchronization occurs, the Merge Agent detects if there are any conflicts. If conflicts are detected, the Merge Agent uses a conflict resolver (which is specified when an article is added to a publication) to determine which data is accepted and propagated to other sites.

Merge Replication automatically resolves conflicts such as 2 subscribers updating the same row with different information.  The “loser” information is recorded in a Merge Replication system table with name


Use the following queries to explore “losing” conflicts.

 — Returns Conflict Table Names


 — Show Update/Insert conflicts

sp_helpmergeconflictrows @conflict_table = ‘MSmerge_conflict_MergeConflictTest_Customer’

 — Show “delete conflicts”


select * from sysmergearticles

select * from MSmerge_conflicts_info

Advanced Merge Replication Conflict Detection and Resolution (SQL BOL)

“The winning row is applied at the Publisher and Subscriber, and the data from the losing row is written to a conflict table.”  You can use the SQL Server Management Studio GUI Conflict Resolver to:

1) mark “loser” as new “winner”


2) delete the “loser” conflict tracked information.

When you delete the conflict data you are affecting the conflict tracking information, the subscriber tables have already been updated when Merge Agent
automatically “resolved” the conflict.

Running the Conflict Viewer from SSMS studio is great if you only have a few conflicts to purge.  But what if you ran a BATCH job on 2 subscribers, modified 1 million rows, but the script updated each subscriber’s row with a different “LastModifiedDate”.  Now the Merge Agent detects the same column being updated at 2 different subscribers for the same row, CONFLICT!  Yes, the Merge Agent will resolve the conflict, but also log into the MSMerge_Conflict tracking table 1 million conflicts.  That’s a lot of mouse clicks to cleanup the conflict tracking tables.

As I mentioned, the conflicts have already been resolved.  The tracking tables just provide a way to “override” the actions taken by the Merge Agent.  If all conflicts should be saved as they were resolved by the Merge Agent, then the conflict tracking data can be purged.  You can use the script below to automatically purge all the update conflicts from the conflict tracking tables.  As noted in the script comments test the script in your environment then  backup your published database before running this script in order to provide a fall back should something unexpected occur.


This TSQL script is used to purge all Merge Replication conflicts as “Resolved”


This script is provided “as is” and should only be run after

a full database back has completed.  This ensures ability to rollback

should any unexpected problems occur.




–Retrieve list of Conflicts to be purged as “resolved”

—    additional criteria can be added as needed such as

—          SELECT TOP 50000 s.conflict_table, c.rowguid, c.origin_datasource

—          WHERE = ‘Customer’

—          WHERE c.origin_datasource = ‘<subscriber>.AdventureWorksLT_Sub1’

—          WHERE c.reason_code = 2  (The same column of the same row was updated)

SELECT s.conflict_table, c.rowguid, c.origin_datasource

      INTO #temp_conflicts

      FROM dbo.MSmerge_conflicts_info c

      JOIN sysmergearticles s

      ON c.tablenick = s.nickname


–Setup local variables

DECLARE @conflict_table nvarchar(255)

DECLARE @row uniqueidentifier

DECLARE @origin_datasource nvarchar(255)


–Step through conflicts and purge by RowGuid

DECLARE conflict_cursor CURSOR FOR

   SELECT conflict_table, rowguid, origin_datasource

   FROM #temp_conflicts

OPEN conflict_cursor;

FETCH NEXT FROM conflict_cursor INTO @conflict_table, @row, @origin_datasource;




      –Purge conflict as “resolved”

    EXEC sp_deletemergeconflictrow

      @conflict_table = @conflict_table,        — conflict table name from sysmergearticles

      @rowguid = @row,                                      — row identifier from msmerge_conflicts_info

      @origin_datasource = @origin_datasource   — origin of the conflict from msmerge_conflicts_info


   –Retrieve next conflict to purge

   FETCH NEXT FROM conflict_cursor

   INTO @conflict_table, @row, @origin_datasource;



CLOSE conflict_cursor;

DEALLOCATE conflict_cursor;

DROP table #temp_conflicts


Share this Post

About: ReplTalk

3 thoughts on “Clearing Resolved Merge Replication Conflicts”

  1. Hi Chris,

    Very nice info on Repl.

    I am former colleague as Sql Pfe and learning much from repl thanks to your articles. Is there a way to submit loser for many rows at a time other than using conflict viewer and click 1 by 1 submit loser??

    Thanks for your time

  2. Very nice info here,

    but i'm still wondering is there any possibilities to check or show conflicted record by it's PK using pl/sql query, not using conflict viewer tool ?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.