Cannot insert duplicate key in object (after replication)

Question

Cannot insert duplicate key in object (after replication)

tarr94

SSCommitted

Points: 1684
More actions
February 5, 2018 at 8:53 am

#410212

Hi,
We have a replication job that copies data from our production environment into a testing environment. As far as I can tell, the replication completed successfully, and I'm not sure the issue is being caused by the replication or not.
We have a table containing an auto-incrementing PK column that runs from "1" to "3000". However, when we try to insert a new record, we're getting a constraint violation error:
"Cannot insert duplicate key in object 'MyTable''. The duplicate key value is (1000)."
When I try running the insert again, the error changes a bit:
"Cannot insert duplicate key in object 'MyTable''. The duplicate key value is (1001)."
Clearly, there is a disconnect in which SQL Server thinks the next available Pk value much lower than it should be. How can I fix this?
I greatly appreciate any assistance!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Looks as if either somebody is overriding the identity property by setting identity insert on, or the identity has been reseeded. Is the table in question in the publication database, or the subscription?

DBCC CHECKIDENT ('dbo.MyTable', NORESEED)

John

tarr94 SSCommitted Points: 1684 More actions · Answer 2

Thanks for the response, John.

This is occurring in the subscription (test) DB.

After running the script you gave me, I'm getting the following message:

"current identity value '1000', current column value '3000'"

By using the following script, am I able to resolve this issue for the time being?

DBCC CHECKIDENT ('dbo.MyTable', RESEED, 3001)

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Yes, that would probably stop the errors. But you ought to find out why they're happening. Is it replication that's attempting to do the inserts? If so, either the article for that table isn't set up properly, or something's gone wrong with it. If it's user activity doing the inserts, you need to decide whether that's permitted. If it is, you're probably going to get more conflicts in the future. If it isn't, perhaps you could lock it down with permissions to prevent it?

John

tarr94 SSCommitted Points: 1684 More actions · Answer 4

You're right, we'll need to take a closer look at what might have caused the seeding to become misaligned, but It looks like that script may have resolved our issue for the short term.

Thanks, John!