Post reply

Runaway Identity column values

  • February 13, 2006 at 12:28 pm

    #167100

    I have an interesting problem. 

    A simple table with a primary key int value.   The PK is defined as an identity field.  The identity field is set to increment starting at 1 and by 1.   I am not doing remote replication.  

    When  inserting the data, the identity values go up not by 1, but by 27 every 6 minutes.   If I do a DBCC CHECKIDENT (Customer, noRESEED) I get the followin in return:

    Checking identity information: current identity value '327422', current column value '327341'.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    OK, so the next value that the system is going to assign is 327422, When checking the table though, no inserts have been completed....and it was 18 minutes from the last insert?

    Datetime                           count(*)      max(custid)

    2006-02-13 13:12:41.863     4166           327340

    Any ideas why the identity field jumps every 6 minutes?????? 

    Thanks in advance

    Eric Peterson

  • February 13, 2006 at 12:34 pm

    #620511

    If an attempt is made to insert a value in a table with an identity value and it fails the identity value still increments.

    I would run a SQL Profiler to try and find out who or what is blasting your customer table.

    LinkedIn Profile

  • February 13, 2006 at 12:43 pm

    #620514

    Thanks, I am having the application folks check their code again.

    But the interesting thing is that it increments by 27 every 6 minutes, even in the middle of the night when the online users are not in the system. 

    EP 

  • February 13, 2006 at 1:04 pm

    #620522

    SOLVED!

    Ok, the issue was just as stated.....there was a process that ran every 5 minutes to do imports.    They disabled the error notification because they go tired of seeing the errors and not fixing the problem. 

    There were 27 rows in the IMPORT table with bad data......   as such every 5 minutes the IDENDITY column went up by 27 even if no activity was going on in the system, as those imports errored 27 times....incrementing the IDENTITY, but inserting no records. 

    Thanks for pointing us in the right direction...

    EP 

     

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply