Why do my SQL Server identity values get out of synch causing gaps/duplicates etc.?

A. 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 Microsoft 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) 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.

(This error should not occur with SQL 7.0 and above as Microsoft have re-worked how the identity columns function internally)