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
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 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
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
FETCH NEXT FROM conflict_cursor INTO @conflict_table, @row, @origin_datasource;
WHILE @@FETCH_STATUS = 0
–Purge conflict as “resolved”
@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;
DROP table #temp_conflicts
3 thoughts on “Clearing Resolved Merge Replication Conflicts”
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
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 ?
Really helpful info. Straightly solved my issue. Thanks