Chris Skorlinski Microsoft SQL Server Escalation Services
Content provided by: Steve Dybing, Microsoft SQL Server Support
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
-- 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
"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