Sql-server – How to delete duplicate rows in merge replication

deletemerge-replicationreplicationsql server

Somehow two rows in a table that is party to merge replication are identical, including their rowguid. I have no idea how this happened, it is only with two rows in my entire database (which is used in production).

No matter how I try to delete them, I get the following error:

Msg 2601, Level 14, State 1, Procedure MSmerge_del_12B5E838BB91458D81AD66DD7EB5ABDC, Line 46
Cannot insert duplicate key row in object 'dbo.MSmerge_tombstone' with unique index 'uc1MSmerge_tombstone'. The duplicate key value is (7094001, df2e61b2-3d8e-e511-84bb-00155d00c1da).
The statement has been terminated.

This is somewhat expected, but I have no idea how to get around it. I'd very much like to avoid dropping anything big (i.e. replication, tables, etc.). This is an isolated case and I don't expect it to happen again so I don't mind doing a manual work-around.

Can anyone provide some insight into this?

Thanks!

Best Answer

The duplicate rows in the MSmerge_tombstone occurs when you restore the database with KEEP_REPLICATION bit.

Its a good practice to configure replication from scratch since you never know that you run into some unknown issue just like you did.

As confirmed by OP - drop the index uc1MSmerge_tombstone, delete the offending row and recreating the index fixes the problem.

Remember that you have to be caution when fiddling with MSmerge* tables.