What could damage the IDENTITY column?

Question

Yesterday I had an unusual problem when I unexpectedly failed to insert records into a table with an identity column.

A simple insert is: INSERT INTO MyTable (Column1, Column2) VALUES ('text', 236764)

The reset of the primary key constraint violation will begin.

I ran DBCC CHECKIDENT on the table and realized that SQL Server stopped updating the last used value, so when it inserted it, it increased using the old value and the new identity value that usually existed in the table, hence the violation.

Solving the problem was not a problem, I just moved the table for the next highest serial number, but I had never seen this before!

Does anyone know what can cause SQL Server to stop updating authentication properties and where can I look for evidence? There is no replication or any triggers involved, it's just a plain old table.

EDIT: SQL Log Rescue would be ideal, but it only works on SQL Server 2000. Is there a similar tool for SQL 2005 logs?

Answer 1

If someone inserted into the table using SET IDENTITY_INSERT ON , someone could absolutely enter the wrong value for the table. That would be my first guess. You can use a log analyzer like SQL Log Rescue to get back in time through transaction logs and see if you can find who the bad person was who messed up your data ...

Answer 2

I think SET IDENTITY_INSERT ON reseeds Identity.

From BOL

If the entered value is greater than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.

The only way to reproduce this problem is to manually set the value too small using DBCC CHECKIDENT.