Identity Column Out of Sync

Question

I have a table with an Identity column as Primary Key.

All is well until one several days ago, application that is using this table start complaining about PK violation. At first I thought this was impossible, until I remember about DBCC CHECKIDENT. The magic function told me that the 'current column value' is higher than 'current identity value'. I RESEED to the highest value and all seemed well again.

My question is, to prevent this from happening again in the future, what are the possible causes of this out of sync problem? And how to prevent it?

Answer 1

It sounds like you would have to search your code to find instances where IDENTITY_INSERT is turned ON, then a (probably high-numbered ident column) key is inserted. Your application has probably gotten lucky in the past in that the inserted (and arbitrary) PK value is within the seed value - probably due to deletions and such.

Answer 2

Identity insert should not be turned on in a production environment unless you are doing scheduled maintenance and are in single user mode during off peak hours. It affects anyone who tries to insert a record (your normal insert process will error because it is not specifying the identity) while it is turned on and using it is a very bad practice! If you have developers or processes using this on your prod environment, you need to immediately rethink your process.

Developers should not have production rights and just that step alone may prevent a future reoccurance of your problem as a dba would not allow identitiy insert to be turned on without thought as to what it would affect. I agree with Josh, check any ETL imports that are being run, in particular, look for one that ran about the time the problem started.

If you have developers changing identity values or turning identity insert on, you need to educate them on why this is a very bad practice. Identity values should not be changed once they are inserted as that affects all related tables as well.