Identity column value falling behind randomly

Question

We are having a strange intermittent issue where the identity column value falls behind the MAX(ID) for the table.

Each customer gets their own table that stores statistics to the tune of [dbo].[Stat_customer1], [dbo].[Stat_customer2], etc.

Randomly, one of these tables' identity values will fall behind, stopping any inserts from happening and we have no idea why.

Here's one example:

DBCC CHECKIDENT ('[dbo].[Stat_customer1]', NORESEED); 
GO

Checking identity information: current identity value '43487', current column value '52012'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SELECT MAX(ID) FROM [dbo].[Stat_customer1]

52012

To repair this we then do:

DBCC CHECKIDENT ('[dbo].[Stat_customer1]', RESEED, 52012); 
GO

Checking identity information: current identity value '52228', current column value '52228'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

We have about a thousand of these tables on average per SQL server. This will only occur on one random table, not all of them. It does not happen every day. Sometimes a week goes by with no issue.

Data is imported to these tables every five minutes by an application that performs a bulk insert. Every night, a SQL job is executed that rolls up statistics by date. E.g. anything over a month old is summed up by month, anything over a year old is summed up by year.

During this time, the application pauses importing data so no new data is imported during the roll-up. Data is read out of table into a temp table and is aggregated as it is being read out. We then delete the old data from the table and insert the new aggregated data from the temp table.

We are not sure if it is the bulk insert or the statistics roll-up that is causing the issue.

This issue started popping up after we upgraded from SQL 2008 R2 to SQL 2012 and virtualized the server.

Does anyone have any clues as to why this is happening?

Answer 1

Randomly, one of these tables' identity values will fall behind, stopping any inserts from happening and we have no idea why.

Inserts are probably stopping because of an attempt to reuse an already existing unique value in the PRIMARY KEY, thus triggering the error like:

Msg 2627, Level 14, State 1, Line 27
Violation of PRIMARY KEY constraint 'PK__ID__1234'. Cannot insert duplicate key in object 'dbo.MyTable'. The duplicate key value is (8).
The statement has been terminated.

Note that if your IDENTITY column does not have a UNIQUE index or constraint, it is possible to reseed repeatedly and have many identical ID values. You do not want to do that, of course.

I have not personally found an error that, in itself, would reseed the IDENTITY value. Of course, it is possible to reset the SEED to a range where there will soon be a conflict by running a reseed that is lower than the current seed:

DBCC CHECKIDENT( MyTable,RESEED, 7) WITH NO_INFOMSGS 

It could be that code somewhere in one of the processes actually does a RESEED on the table under some unusual circumstances.

(For example, this could be from a merge of two data sets, where the code reads the high value from one data set and after the import RESEEDs to the lower of the two high values that were merged.)

You should also read Martin Smith's post at: https://stackoverflow.com/questions/14146148/identity-increment-is-jumping-in-sql-server-database