What could cause an IDENTITY column to become corrupted?

Question

I had an unusual problem yesterday where I was suddenly unable to insert records into a table with an identity column.

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

Started throwing a primary key constraint violation.

I ran DBCC CHECKIDENT on the table, and realized that SQL Server had stopped updating the last used value, so that when it was inserting it was incrementing using the old value and the new identity value usually already existed in the table, hence the violation errors.

Resolving the problem wasn't an issue, I just reseeded the table for the next highest sequence number, but I've never seen this happen before!

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

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

Answer 1

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

Answer 2

I think SET IDENTITY_INSERT ON reseeds the Identity.

From BOL

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

The only way I could reproduce this issue was to manually set the seed too low with DBCC CHECKIDENT.