What is best way to resolve replication error

Question

What is best way to resolve replication error

Gordon Pollokoff

SSCommitted

Points: 1930
More actions
June 7, 2005 at 5:45 am

#89387

Receiving the following error:
Cannot insert duplicate key row in object 'MSmerge_contents' with unique index 'uc1SycContents'.
I am using merge replication and virtually all of the changes occur at the subscriber ( don't bother asking why we are doing this....its a long story)
Replication had been proceeding fine then started receiving the above error. I am not sure how best to correct. Any help would be appreciated.
Thanks,
Gordon

Gordon Pollokoff
"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

BReuter Ten Centuries Points: 1325 More actions · Answer 1

Somehow, two different subscribers must have inserted the same GUID in the column defined as ROWGUIDCOL on the same table. I'd say this is worth opening a support case to MS as the clean-up will get quite involved. If this is not a production environment and you want to play the clean-up will be something like this:

1) Find the subscriber, table, and row that is causing this error. (You'll probably have a tablenick= value and rowguid= value from the error message. Query sysmergearticles where nickname = to find the tablename)

2) Save all of the details of the row on the problem subscriber as we'll have to remove the row and re-insert it with a new GUID.

3) Disable the triggers on the subscriber for the table in question, delete the problem row, and delete from the following replication tables on the subscriber.

delete MSmerge_contents where rowguid = @problemrowguid
delete MSmerge_tombstone where rowguid = @problemrowguid
delete MSmerge_genhistory where guidsrc = @problemrowguid
delete [tablename] where [rowguid] = @problemrowguid

4) Enable the triggers on the table, re-insert the deleted row with a new guid using newid()

5) Finally, sync.

This obviously won't be supported so your best bet is MS, but if your looking to learn about the merge replication internals, this should get you started.

Gordon Pollokoff SSCommitted Points: 1930 More actions · Answer 2

Thanks for the suggestion. I ended up tracing through all the calls made by the replication process until I found the root error. The root error pointed to a missing RID. A quick dbcc dbreindex and lo and behold, replication worked.

While I can't fully explain the cause of the error or why the solution was re-indexing a table, I can't complain about the final outcome.

Gordon

Gordon Pollokoff

"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones

john brathwaite Valued Member Points: 73 More actions · Answer 3

Hi, I got the same error but found that this was because there were duplicate replication triggers on the table causing the problem. An old replication publication had been removed but the related triggers had not been deleted. I deleted/dropped the triggers relating to the old publication and no longer got the duplicate key insert error.

Cheers John

Carl K SSC Enthusiast Points: 122 More actions · Answer 4

I also had this problem and then found that the collation was set wrong for the subscribers database which I had created with a script. As soon as I dropped the database and re-created with correct collation it worked.

Cheers

Carl

marcos jose Old Hand Points: 309 More actions · Answer 5

Hi,

I had the same problem with sql server 2000 SP4. Really, dbcc dbreindex is great to erros, and very fast.

Thanks.