I have migrated my publisher DB to new server yesterday. For migration what I did was

  1. removed subscriptions and publication completely
  2. backed up DB on which publication was configured
  3. restored DB to new publisher server
  4. Ran sp_removedbreplication on newly restored DB to give a clean start
  5. recreated publication and subscribers on my respective servers

I was able to setup replication properly and it came in sync normally. But when user tried to insert new records at subscriber I got primary key violation errors for lot of tables. Primary key is on identity columns for most of my tables and have not for replication true on both publisher and subscriber end. Upon checking the identity check constraint on subscriber I saw that it had old ranges than my current max values. It wasn't getting the new ranges even though it had automatic identity range management set. I had to manually RESEED the identity on subscriber to make it work and had to go through manual process of reseeding it for all tables. My environment is :

Replication type: Transaction replication with updatable subscription with immediate updating

  1. 1 publisher -- SQL server 2008 R2 + SP2
  2. 1 distributor -- SQL server 2008 R2 + SP2
  3. 2 subscribers -- SQL server 2008 R2 + SP2

I have below questions:

  1. What could be the reason for automatic identity management not working?
  2. How to fix this problem permanently? Is there a script available which can check my max values on publisher and subscribers and then RESEED it accordingly?
  3. What is the fastest way to fix this?
  • I think that maybe when you move the distributor the clean up job don't has execute yet. Do you get the id and check if it's exists in the destination?
    – Krismorte
    Commented Feb 22, 2016 at 11:20
  • Yes, ids do exist in destination. Don't know how clean up job would be related as I recreated replication from scratch.
    Commented Feb 22, 2016 at 13:13
  • The Clean up job clear the commands that alread in the destination. During the migration you maybe to run this job and now SQL is try to delivering the commands in the queue.
    – Krismorte
    Commented Feb 22, 2016 at 13:17
  • These are coming after the synch got completed.
    Commented Feb 22, 2016 at 13:24
  • Really, really strange.
    – Krismorte
    Commented Feb 22, 2016 at 13:28

1 Answer 1


My solution for your problem is skip the primary key error in the replication.

Through the SQL Monitor create a Agent Profile with the code 2627 and let the primary keys error pass. After that the replication should be normal again.

When you create and mark the agent profile you will need stop and run the subscriber job.

  • But will it fix the automatic identity range management? I think highly unlikely.
    Commented Feb 22, 2016 at 13:42
  • Here says that Republishing data is not supported. technet.microsoft.com/en-us/library/ms151718(v=sql.105).aspx
    – Krismorte
    Commented Feb 22, 2016 at 14:00
  • I am not republishing any data.
    Commented Feb 22, 2016 at 14:15
  • I'm trying to understand how possible a backup\restore process could be reset the identity
    – Krismorte
    Commented Feb 22, 2016 at 15:19
  • Guys, Any other suggestions?
    Commented Feb 24, 2016 at 6:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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