July 8, 2013 at 4:00 pm
Hello,
We have sql 2000 replication in our environment. recently, we have been getting error saying 'Violation of PRIMARY KEY constraint *sayabc_PK*. Cannot insert duplicate key in object *sayabc*. I am not quite good with replication technique. from error, I know its duplicate record have been inserted or found and that is why the distribution agent has stopped working. I know the temporary solution is I can configure dist. agent to be keep working on consistency errors found. Is there any other solution? also How I can find the duplicate record in replication database (more specific, where to look for duplicate record? publisher or subscriber?)
all the help/suggestions will be appreciated greatly.
thanks in advance
July 12, 2013 at 9:20 am
let's do it step by step.
Replication tries to copy a record from publisher to subscriber. If it tells you a PK violation one of the records to be transferred already exists on the subscriber. So you have too find the value on the subscriber!
I'm not sure of the error messages in SQL 2000 but I think it gives you at least the name of the stored procedure that failed. This way you know on which table the error occured (you know this too by the name of the primary key named in the error message).
How to find the primary key value?
Have a look again at your error message. PK violations usually contain the value that could not be inserted.
Another possibility: the error message should contain the xact_seq_nr or something similar. With this you can use the browsereplcmds-command on the distribution database to get the exact command that raises the error. This command should contain the primary key value.
Example:
exec sp_browsereplcmds @xact_seqno_start = '0x0003BB0E000001DF000600000000', @xact_seqno_end = '0x0003BB0E000001DF000600000000'
Next possibility: Use the profiler. As you already know the procedure that the replication executes create a trace that shows you the executions of this procedure. You should get the exact input parameters here.
If you finally found the PK value, what you do now?
As always, it depends.
If the table on the subscriber is only for read operations and is never changed just delete the record from the subscriber. Replication will insert it with all data of the publisher.
If you change the records on the subscriber you cannot just delete it as the changes done on the subscriber get lost. You could store the record on the subscriber, then delete it. After insert of the replication you could manually update it with the saved values.
Hope that helps.
July 23, 2013 at 6:06 am
We were plagued by this issue several years ago (SQL2K5 publisher, remote distributor, 25 transactional subscribers in a 24/7 OLTP setup), and it was a real pain as the data set size meant reinitializing all the subscribers took well over 24 hours, along with all the associated customer service issues with out-of-date data.
I also was suspicious about using the "Continue on data consistency errors" agent profile. However we tried one subscriber with the profile, and on the next few PK violation incidents I made sure to follow the procedure mentioned by Wolfgang to check the replication transaction that was at fault. In all cases the the data had been successfully delivered by a previous replication transaction, so there was no data inconsistency. I also used Data Compare to check the table contents and it was absolutely in sync.
The issue appears to be a bug in replication.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply