The identity column (which is also the primary key) in one of my tables is occasionally getting out of synch so that when I try to insert a new record it fails because the primary key is no longer unique. Running DBCC CHECKIDENT() fixes the problem but I would much prefer to understand how the problem is caused in the first place. The fact that the DBCC command exists means I'm not the only person in the world that has encountered this error. Do you know how to avoid this error?

-- Anonymous, March 15, 1999

Answers

Martin,

Neil Pike MVP/MCSE of Protech Computing Ltd. offers this answer at microsoft.public.sqlserver.server.

Q. Why do my identity values get out of synch causing gaps/duplicates etc.? Is there anything I can do about it? (v1.0 08.10.1998)

A. Why? Because of inherent problems with the way they were implemented. For performance reasons the current identity value isn't updated and committed in the system tables every time a row is inserted. This would lead to unacceptably bad performance, especially with SQL 6.x's page-level locking architecture - it could even lead to deadlocks. Therefore the value is stored in memory and only committed to disk when a clean shutdown of SQL occurs.

So, if SQL doesn't shut down cleanly, or there is some memory problem caused by an exception violation then the value will not be correct next time SQL starts. There are also some other bugs that would cause the value not to be updated, but MS fixed most of these with 6.5 SP3.

The only thing you can do about it is to put dbcc checkident() statements in a startup stored-procedure (details of this in the BOL [SQL Server Books Online]) so that the values get fixed every time SQL starts - obviously for very large tables this may take a few minutes.

MS's own code/stored-procedures are not immune to this. One very common case is where you get duplicate key messages on sysbackuphistory (in MSDB) when you do a database dump. This is because the table uses an identity column.

(MS promises this situation will not occur with SQL 7.x as they have re-worked how the identity columns function internally.)

Hope this helps,

Eric

-- Anonymous, March 15, 1999

---

Moderation questions? read the FAQ