This question is about strange behavior in SQL Server 2008 R2 SP3 Standard Edition x64.
I have read many questions about IDENTITY increments in SQL Server and strange gaps between them. But basically people say it is related to transactions or restarts. In my case (using a named instance) gaps are simply gigantic.
Application inserted 130 000 records in table, and "Arithmetic overflow IDENTITY conversion to int" error occurred. We truncate the table, and bulk insert 70 000 records. During bulk insert IDENTITY column maximal value is above 9 000 000!
As it was not normal load but mass insert - there was no rollback, restart nor any other issue during this operation.
So gaps are not in the size of 1000 but tenths of thousands.
As I do not control application code (it is commercial software bought from other company) I am simply afraid what can I do as Administrator of such database? Have I to truncate table once every 6 months? Monitor in real time ID column?
Gaps are not in the size of 1000 but tenths of thousands. Are there any pupils experiencing similar issue?