Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite
1

Okay, a little complex:

  1. One local DB table is being replicated to another local DB with same table. Worked for years. User account on replication job is gone, so job was failing. I updated it to a different account, and every 10 minutes, it had no errors then. However, it didn't replicate a new row.
  2. I manually created the new record in the subscriber DB table.
  3. Restarting the server ended up working, but now the replication fails because it has the queue of new records it's trying to add and the identity column value already exists in the destination table.

Options: Should I...

  1. Delete the records I added so that the replication can copy down the new records?
    • Problem: Not sure if the used IDs will be able to be used with the new records coming over in the replication (because of the auto-numbering identity column), and we need those IDs to remain matched.
  2. Somehow clean out the queue so that those records aren't attempted any more (since I already created them) -- not sure how to do this.

Yes, I searched a lot on this stuff, but I haven't seen this problem described in the same way.

Thanks for any help or answer!

share|improve this question

2 Answers

up vote 3 down vote

You do not need to drop the subscription.

You're getting primary key violations as the replication agent is trying to insert duplicates.

  1. Delete the records I added so that the replication can copy down the new records?

Yes, you will need to manually remove the records in order to alleviate the primary key violations.

share|improve this answer
2  
Just to drive this home, don't ever make manual DML changes at the subscriber. The whole topology makes assumptions based on it being the only thing changing data there. Change those assumptions and the whole thing falls apart. – Ben Thul Mar 15 '12 at 21:09
up vote -1 down vote
  1. You need to re-create your publication
  2. Drop the subscription
  3. Sync data from old subscriber database to the publisher - use something like Redgates Data Compare, it will generate the change scripts for you - OR write you own sync script
  4. re-create the subscription
share|improve this answer
Okay, I know they're in sync, so I'll just drop it and recreate it. Thanks! – Jeff Mar 15 '12 at 17:17
That's not the correct solution to the problem! While it will work well in the end, it's like getting an axe at a barber shop :-). There are solutions to replication synchronization issues. Please see my question and the details in it (plus the answer and the comment). – Marian Mar 15 '12 at 20:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.