1

This question is about strange behavior in MS SQL 2008 Server Standard R2 SP3

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 ( we are using named instance of MS SQL Server 2008 R2 Standard 64 bit SP3) 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 simple 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?

  • possible duplicate of :http://stackoverflow.com/questions/17587094/identity-column-value-suddenly-jumps-to-1001-in-sql-server – CeOnSql Aug 28 '15 at 07:27
  • how did you create the identity columns. It is possible to define the size of the gaps – t-clausen.dk Aug 28 '15 at 07:27
  • why can't you use DBCC commands before inserting the data into the table then gaps will not Increase – mohan111 Aug 28 '15 at 07:27
  • possible duplicate of [Arithmetic overflow error converting IDENTITY to data type tinyint](http://stackoverflow.com/questions/27040938/arithmetic-overflow-error-converting-identity-to-data-type-tinyint) – mohan111 Aug 28 '15 at 07:29
  • @CeOnSql that question concerns sqlserver 2012. There was a new identity issue in 2012, which was fixed in 2014 – t-clausen.dk Aug 28 '15 at 07:29
  • @Jonathan tinyint can only hold the values 0-255. I doubt gaps of tenths of thousands can be an issue with tinyint, This is not a duplicate of that – t-clausen.dk Aug 28 '15 at 07:35
  • 1
    you could change the column type to big int, that will change the gaps, but it can prevent the overflow for a long time – t-clausen.dk Aug 28 '15 at 07:42
  • @t-clausen.dk Ok i agree with you – mohan111 Aug 28 '15 at 07:44
  • It is about MS SQL 2008 Standard R2 SP3. NOT SQL 2012!!! This strange behavior occurs during load data from application and I have no control on what it insets data. There was no restart, and gaps are very big ( not 1000 as in questions You mentioned) –  Aug 28 '15 at 08:05
  • You could monitor the SQL statement which inserts the data. It is possible to set the id with INSERT or UPDATE, if you set the IDENTITY_INSERT option to "ON". Could be, that the inserting application is poorly done... – Shnugo Aug 28 '15 at 08:13

2 Answers2

0

Sorry, not a solution per se but you can confirm the seed value and increment value from the following sys table;

select
      object_name([object_id]) as [objectname]
    , [name]
    , [seed_value]
    , [increment_value]
    , *
from sys.identity_columns

increment_value is typically 1 but if it were 100 for example then after inserting 10 new rows your identity value would have advanced by 1,000.

Edward Comeau
  • 3,874
  • 2
  • 21
  • 24
0

The posssible reson for gaps in identity is when the insert statements rollback. That you are using the third party application, it might be a culprit in this regard.I would therefore suggest that you create a dummy table to test your assertion as below, see if sql server causes gaps:

create table TestGaps
(
    id int identity(1,1)
    ,someValue char(5)
)
go
with cte as
(
    select 1  as rn,'ABCDE' as val
    union all
    select (rn + 1), val from cte where rn<32767 
)
insert into TestGaps(someValue)
select val from cte option(maxrecursion 32767)
Mazolo
  • 307
  • 4
  • 19