0

I have a table with an identity column that have incremented by 1 for all its records then suddenly last week it started incrementing in a weird way.
For example:

Identity - Date

31891 2010-02-27 09:47:41    
39258 2010-02-27 13:13:03    
41994 2010-02-27 13:43:56   
45119 2010-02-27 14:44:43    
45120 2010-02-27 14:51:33
45121 2010-02-27 15:01:12    
63943 2010-02-27 19:38:24    
63944 2010-02-27 20:14:11   
73344 2010-02-27 22:06:35   
78298 2010-02-27 22:54:43    
124007 2010-02-28 07:02:51    
130482 2010-02-28 07:35:34    
130483 2010-02-28 08:05:02    
144470 2010-02-28 10:11:57    
164253 2010-02-28 11:30:00   
254921 2010-02-28 16:59:51    
254922 2010-02-28 17:06:28

If you look at the time you can see that the longer time it is between inserts the highter the increment is. I am totally lost to why this is happening. I do not perform any delete queries on the table so it can't be deleted rows.

ann
  • 576
  • 1
  • 10
  • 19
Gusten
  • 3
  • 1
  • I was going to say rows are being deleted somehow, this is strange behavior, have you considered watching the table with Profiler to see what is going on under the covers? – Ta01 Mar 05 '10 at 14:20
  • Can you show us your table definition, the DDL for creating the table? – Will Marcouiller Mar 05 '10 at 14:21
  • Have you tried to reseed the identity? – Rob Packwood Mar 05 '10 at 14:21
  • The table could also be being reseeded at a higher-than-current number. This is of course ridiculously unlikely, which is why I'm mentioning it as a comment, not an answer. – Philip Kelley Mar 05 '10 at 14:51

2 Answers2

6

An identity sequence is incremented any time an insert is attempted -- this includes a failed or rolled back INSERT. Do you have any processes generating insert errors or rolling back?

Eric Kolb
  • 996
  • 6
  • 9
  • i see now that the partition sql server is on (and windows) is out if disk space, the db files are on another partition that has lots of diskspace but can it affect the inserts? – Gusten Mar 05 '10 at 14:29
4

Each failed insert results in the identity value being incremented. Is it possible something is trying to insert new rows and failing?

TLiebe
  • 7,913
  • 1
  • 23
  • 28