SQL Server 2005 identity incrementing by itself

Question

Hey.

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 cant be deleted rows.

Answer 1

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?

Answer 2

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