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
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?