June 14, 2011 at 2:01 pm
Hi,
I have setup Transaction replication and it was working fine on SQL 2000. I am getting error on Dist. Agent for Primary key violation. I even changed Dist. Agent Profile to "Continue on data consistency errors". Still I am getting error.
I CAN'T drop or reinitialize replication. How can I skip this error for the time being and check for duplicate keys later.
June 14, 2011 at 2:41 pm
You could delete the duplicate record on the subscriber and that would allow replication to insert the identical record. You could also delete the transaction from the distribution database by getting the transaction id that is failing from replication monitor. The delete from the distribution database is a bit more involved and requires a delete from 2 tables, msrepl_transactions and msrepl_commands. AND if you were to do that you would lose other commands in that transaction, if there were others.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
June 14, 2011 at 3:03 pm
Thanks for reply.
I couldn't see tables on Subscribers as I initialized snapshot to replicate these tables.
1. Error messages are getting changed when I click Start Synchronizing on Dist. agent. Does that mean other transactions which are not violating are getting replicated?
Violation of PRIMARY KEY constraint 'PK__@snapshot_seqnos__3B9B84A9'. Cannot insert duplicate key in object '#3AA76070'.
Violation of PRIMARY KEY constraint 'PK__@snapshot_seqnos__3D024829'. Cannot insert duplicate key in object '#3C0E23F0'.
2. I saw that we are on SQL Server 2000 SP2 (8.00.760) and MSFT suggests to apply SQL Server 2000 Service Pack 3 rollup
http://support.microsoft.com/default.aspx?scid=kb;en-us;813494
3. How can I see transactions in Distribution and delete those?
June 14, 2011 at 3:11 pm
I have to step away but can help later again. In the meantime, are you sure that the snapshot completed successfully? Are you seeing the error in the distribution agent? Based on your latest post it appears to be a snapshot agent error. Please confirm this. Thanks.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
June 14, 2011 at 3:20 pm
Snapshot agent says:
A snapshot of 14 articl(s) was generated.
So I assume snapshot is generated but not applied on Subscriber because when I try to see Select count(*) from Table it returns Invalid Object.
Yes I am seeing error in distribution agent.
My concern is that transaction log for distribution and published databases don't get filled. Should I stop LogReader or keeping it running will replicated rows which are not violating PK contraint?
June 15, 2011 at 7:46 am
Log Reader will continue to move the records from the transaction log to the distribution database. You will want to leave that running so you don't get t-log bloat. The distribution database will however grow due to the fact that it cannot deliver replicated transactions.
Honestly, if this is a new publication with new tables I would drop the publication and recreate it. Not sure what is going on with the PK but you may want to check your data on the publisher.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
June 15, 2011 at 7:54 am
are you replicating triggers as well?
the PK violations could be caused by a trigger at the subscriber.
if not then have a look at xp_replcmds and related extended stored procs (i can't remmeber them without BOL in front of me) - you can idenitfy the transactions that are attempting to be pushed to your subscriber and inspect the SQL inside them
MVDBA
June 15, 2011 at 7:57 am
Great point Michael. You could also just run a trace on the subscriber with the application name being that of the distribution agent and you can see what you are erroring out on as well.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
June 20, 2011 at 11:51 am
Thanks for your help. It is working fine now. I was getting this error whenever I created new articles and ran snapshot agent manually. During this process I got PK violation error but once snapshot was applied PK error resolved.
Another question: I created few articles without specifying destination table owner and they are owned by dbo at subscriber. Again I can't stop, drop or reinitialize replication or articles. This is what I assume to perform this or am I missing something.
1. Stop LogReader at Publisher
2. Change table owner at subscriber using sp_changeobjectowner
3. Change article property using sp_changearticle Property = 'destination_owner'
4. Start LogReader
June 20, 2011 at 12:35 pm
The following is according to BOL;
"Changing any of the following properties requires that existing subscriptions be reinitialized, and you must specify a value of 1 for the force_reinit_subscription parameter.
del_cmd
dest_table
destination_owner
filter
ins_cmd
status
upd_cmd"
So, you could drop the subscription, run the command, re-add the subscription without initializing (so that you don't have to push the data again). If repushing the data again is not an issue then you could initialize the subscription as well. If you choose to add the subscription without initializing then you will have to plan to stop inserts / updates to that table or plan to do a manual sync after the subscription is added.
Hope this helps.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply