Clearing Resolved Merge Replication Conflicts

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

“MSmerge_conflict_<publication>_<table>”

Use the following queries to explore “losing” conflicts.

 — Returns Conflict Table Names

sp_helpmergearticleconflicts

 — Show Update/Insert conflicts

sp_helpmergeconflictrows @conflict_table = ‘MSmerge_conflict_MergeConflictTest_Customer’

 — Show “delete conflicts”

sp_helpmergedeleteconflictrows

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”

–or–

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 s.name = ‘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;

WHILE @@FETCH_STATUS = 0

BEGIN

      –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;

END

CLOSE conflict_cursor;

DEALLOCATE conflict_cursor;

DROP table #temp_conflicts

GO