We have "push" SQL replication sent up, with one publisher and three subscribers. Two of the three are working fine, but one is continually causing trouble. I went through the pain and heartache of reinitialising the subscription at the weekend, and just a few days later it's stuck again.
This time, the error I'm seeing in replmerg.log (after turning up the verbosity) is:
The merge process could not replicate one or more INSERT statements to the 'Publisher'. A stored procedure failed to execute. Troubleshoot by using SQL Profiler.
I've tracked this down to a single table in the database: for some reason, an obviously corrupt row is continually trying to be INSERTed into the table, with a rowguid of
DBCC CHECKDB shows no sign of corruption.
Here's the row:
If I try to get rid of the bad row on the Publisher:
delete from mytable where rowguid like '00000000-0000-0000-0000-000000000000'
I get the error:
Msg 2601, Level 14, State 1, Procedure MSmerge_del_590C419D78ED4A36A40292C23CF5F67B, Line 68 Cannot insert duplicate key row in object 'dbo.MSmerge_tombstone' with unique index 'uc1MSmerge_tombstone'. The duplicate key value is (3095029, 00000000-0000-0000-0000-000000000000). The statement has been terminated.
If I manually remove the row from
MSmerge_tombstone, it comes back after the next replication cycle (which is continuous, so typically 60 seconds later).
I really don't want to have to reinitialise this subscription again. Is there any way of purging this set of corrupt transactions out of the "queue", or otherwise returning the replication back to normal?