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?
Best Answer
Inserts are probably stopping because of an attempt to reuse an already existing unique value in the PRIMARY KEY, thus triggering the error like:
Note that if your
IDENTITY
column does not have aUNIQUE
index or constraint, it is possible to reseed repeatedly and have many identicalID
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 theSEED
to a range where there will soon be a conflict by running a reseed that is lower than the current seed: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