1

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!

  • By anychance you restored the database with KEEP_REPLICATION ? You rarely hit a dupe in dbo.MSmerge_tombstone unless a database is restored. – Kin Shah Nov 23 '15 at 20:38
  • @Kin Yes, the subscriber is a restore of the publisher that may have had KEEP_REPLICATION enabled. I will keep that in mind for future restores, if necessary. Is there a way to correct this now though? – tmwoods Nov 23 '15 at 20:58
  • can you try to drop and recreate the index uc1MSmerge_tombstone to see if it fixes the problem ? – Kin Shah Nov 23 '15 at 21:21
  • That worked! Awesome, thanks a lot! If you put that as an answer I'll mark it as correct. I scripted the index first (so I wouldn't have to write it out), then dropped it, deleted the row through T-SQL (can't do it through the Edit pane), then re-created the index. – tmwoods Nov 23 '15 at 21:42
  • Glad that it helped. I have posted as answer to get this out of queue of unanswered questions ! – Kin Shah Nov 23 '15 at 21:52
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.