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!

Kin Shah
62.6k6 gold badges124 silver badges247 bronze badges
asked Nov 23, 2015 at 20:19
5
  • By anychance you restored the database with KEEP_REPLICATION ? You rarely hit a dupe in dbo.MSmerge_tombstone unless a database is restored. Commented Nov 23, 2015 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? Commented Nov 23, 2015 at 20:58
  • can you try to drop and recreate the index uc1MSmerge_tombstone to see if it fixes the problem ? Commented Nov 23, 2015 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. Commented Nov 23, 2015 at 21:42
  • Glad that it helped. I have posted as answer to get this out of queue of unanswered questions ! Commented Nov 23, 2015 at 21:52

1 Answer 1

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.

answered Nov 23, 2015 at 21:51

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.