0

I have a Windows Server 2012 with SQL Server 2012 and IIS running 4 Silverlight (4) applications. From one day, one of the Silverlight applications skipped 10.000 numbers.

Here is a picture of the datagrid to show what the result is:

The problem

Does anybody have an idea what happened here? And how can I make sure this doesn't happen again?

Also I would like to know if there is any way I can solve this so that it will just be like 489, 490, 491, ...

Thanks in advance!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dave
  • 434
  • 5
  • 22

1 Answers1

2

This is a known 'closed as by design' bug / feature of SQL Server 2012.

See connect item Failover or Restart Results in Reseed of Identity

The feature was implemented by Microsoft to fix a regression issue caused by how they handle the new sequence data type. The identity range jump occurs after a instance restart, for all tables defined with an identity value, that have had one or more rows INSERTED since the last time the instance was started i.e any active table.

SQL Server will pre-allocate and jump the IDENTITY range by 1,000 values when the column assigned the IDENTITY value is created as an INT and 10,000 values when defined as a BIGINT.

There are currently 5 workarounds described in the bug report, how ever none of them are terribly palatable. Does it really matter that there are gaps in the sequence, what would happen to your application if a row defined with a IDENTITY value is deleted, or inserted and then rolled back? In both cases a gap in the IDENTITY range would be introduced.

Pixelated
  • 1,531
  • 3
  • 15
  • 35