May 3, 2012 at 10:15 am
This morning I made some changes to an existing publisher.
Original Publisher had 15 tables being replicated using Transactional replication. I edited the articles and unchecked 6 tables and saved the publication. Everything continued to run fine.
I created a new Publisher with the 6 tables I removed and setup the do not replicate DELETE statements for these tables. I created the subscriber to the same subscriber the tables were pointed to on the Original Publisher. I created the snapshot and the New Publication was applied without error.
Then about an hour after I set it up I started getting "Replication failed. Violation of PRIMARY KEY contraint. Cannot insert duplicate key in object".
The error is on one of the tables I moved. The Primary Key is a GUID on the table in question.
We reintialized the New Publisher and the errors went away for about thirty minutes and then came back.
Does anyone know if there is any phantom articles or pieces from the Original Publisher causing problems? Or does anyone have ideas on what could cause this primary key problem or how to troubleshoot?
Thanks
gb
May 3, 2012 at 11:44 am
Is this a push or pull subscription?
I have had something akin to this issue. The publisher kept trying to pass rows that were already there. That is why, when you delete the subscriber's data everything works for 30'ish minutes. That is how long it takes for the publisher to push data to the subscriber.
May 3, 2012 at 11:47 am
The first step that I'd take would be to make sure that the articles are indeed gone. Check the sysarticles table on the publisher. Next, I'd use profiler (or whatever your favorite tool is) to look at what call is failing at the subscriber. Delivery of commands to the subscriber is typically handled by stored procedures. Get a look at which one is failing and what parameters are being passed to it. That should at least give you something to go on.
May 3, 2012 at 11:58 am
A bit intrigued here as well. You are not replicating delete statements. Any chance that you are reusing the primary keys on the publisher and hence getting duplicates at the subscriber? I would definitely get the details with the next failed statement, i.e. profile and get the insert procedure being executed and figure out why that key is already there.
Let me know what you find.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
May 4, 2012 at 4:24 am
USE distribution
GO
EXEC sp_browsereplcmds
@xact_seqno_start = '0x00001C3D00000B9A002800000000',
@xact_seqno_end = '0x00001C3D00000B9A002800000000'
GO above query will give you exact query which his creating PK violation here "xact_seqno" can be obtained from replication monitor
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply