SQL Server Replication Conflict Unresolvable — Fix Now

You hit the wall with a replication conflict that won't auto-resolve

Yeah, that sinking feeling when the replication agent keeps failing and the conflict viewer shows rows you can't seem to clear. I've been there. Let's fix this right now.

The Direct Fix: Purge Stale Conflict Rows

What's actually happening here is that the merge or peer-to-peer replication agent hits a conflict it cannot resolve because the conflict resolver metadata is corrupted or the conflict retention period has been exceeded. The quickest way to unblock this is to delete the conflicted rows from the conflict tables directly.

1. Identify the conflict table. For merge replication, it's usually named MSmerge_conflict_<publication>_<article>. For peer-to-peer, look at MSpeer_conflict_<publication>_<article>. Run this on the publisher:

USE [distribution];
SELECT * FROM MSmerge_conflict_MyPub_MyArticle;

2. Delete the problematic rows. Filter by the conflicting primary key and the conflict type (like 4 = update-update conflict).

DELETE FROM MSmerge_conflict_MyPub_MyArticle
WHERE rowguid = 'some-guid' AND conflict_type = 4;

3. Run the replication agent again. Use SQL Server Agent or replmerg.exe for merge, or distrib.exe for transactional.

exec sp_replmerg '-continuous', '-Publisher', 'MyServer', '-PublisherDB', 'MyDB', '-Publication', 'MyPub', '-Subscriber', 'MySub', '-SubscriberDB', 'MySubDB';

Why This Works

The reason step 3 works after step 2 is that the replication agent checks the conflict tables before applying changes. If it finds a conflict it can't resolve — say because the conflict resolver stored procedure is missing or the conflict metadata points to a deleted row — it errors out and stops. By deleting those rows, you're removing the roadblock. The agent then sees a clean state and proceeds.

But here's the nuance: you're not resolving the conflict, you're discarding it. That's fine if you know the data at the subscriber is correct and you want the publisher to win. If you need the subscriber's version, you better export it first.

Less Common Variations of This Issue

1. Conflict Retention Period Expired

SQL Server has a conflict retention setting (default 14 days). If the conflict is older than that, the system might clean up the metadata but leave orphaned rows in the conflict table. This causes the exact same error. Fix: increase retention or manually clean older rows.

-- Check current retention
SELECT retention FROM MSmerge_conflict_MyPub_MyArticle;
-- Clean rows older than 7 days
DELETE FROM MSmerge_conflict_MyPub_MyArticle
WHERE creation_date < DATEADD(day, -7, GETDATE());

2. Conflict Resolver Missing or Corrupt

If the conflict resolver (a COM object or stored procedure) is missing, the agent can't resolve conflicts. You'll see error 20598 in the agent history. Re-register the resolver:

sp_register_custom_resolver 'MyResolver', 'MyResolverProcedures.dll';

3. Peer-to-Peer Conflict with Outdated Topology

In a peer-to-peer setup, if a node is removed and re-added, conflict tables might reference old node IDs. Drop and recreate the conflict table:

DROP TABLE MSpeer_conflict_MyPub_MyArticle;
-- Then reinitialize the subscription

Prevention: Stop This From Happening Again

The root cause is almost always a mismatch between conflict resolution settings and your actual data usage patterns. Here's how to lock it down:

• Set conflict retention to match your reconciliation frequency. If you reconcile conflicts weekly, set retention to 30 days. Don't leave it at default 14.
• Use a custom conflict resolver when you have predictable conflicts. For merge replication, define a stored procedure that implements your business logic (like "subscriber wins if timestamp is newer").
• Monitor the conflict tables weekly. A simple query on MSmerge_conflict_* or MSpeer_conflict_* will tell you if rows are piling up. If they exceed 1000, something's off.
• Test your replication after any schema change. Adding a column to a replicated table can cause conflict metadata to go stale. Run sp_repladdcolumn properly.

One more thing: if you're on SQL Server 2019 or later, the conflict resolution logic was tightened around temporal tables and computed columns. If you use those, check the compatibility level. A mismatch there can cause unresolvable conflicts that look like this error. Set the compatibility level to 150 or 160 to match the expected behavior.