0

I have a SQL Server 2012 database with a table that has an identity column that has jumped by several hundred million:

  • Identity seed: 10
  • Identity increment: 1

  • Data Type: bigint

    4000012345 to 5000000001

There was potentially a record inserted around the time of the issue with: 

SET IDENTITY_INSERT orders ON
SET IDENTITY_INSERT orders OFF

I’m currently trying to find the TSQL of the record in the transaction log and will update accordingly.

After a restart of the server yesterday this issue was also apparent: SQL Server 2012 column identity increment jumping from 6 to 1000+ on 7th entry

However, I don’t think this issue would explain such a large jump but may be related.

Please can anyone offer any advice on alternative places to look or provide a potential explanation?

The jump of 10,000 I can understand and have observed after restart of server.

I can find very little online of references to such a large jump. I have concerns if this happens multiple times there is the potential for no IDs to be available.

Community
  • 1
  • 1
user1024416
  • 135
  • 1
  • 3
  • 11
  • 4
    It looks like a reseed. However, you cannot rely on consecutive numbers anyways, so this "jump" should not be an issue since there's plenty of room in the `bigint`. – Lucero Oct 20 '16 at 11:35
  • 7
    SQL Server does *not* guarantee that `identity` columns are gapless. In fact, there are many cases where gaps appear (notable only failed inserts and when numbers are "pre-allocated" for bulk inserts). I will admit that your gap does seem rather large. – Gordon Linoff Oct 20 '16 at 11:36
  • I think what you encountered is a reseed based on a new sequence, which would be a duplicate of the Q you referenced. I'm pretty sure this is a duplicate; and because this one is using `bigint` the sequence chunk is just bigger than with a normal int. I'm therefore voting to close as duplicate. – Lucero Oct 20 '16 at 11:38
  • Possible duplicate of [SQL Server 2012 column identity increment jumping from 6 to 1000+ on 7th entry](http://stackoverflow.com/questions/14162648/sql-server-2012-column-identity-increment-jumping-from-6-to-1000-on-7th-entry) – Lucero Oct 20 '16 at 11:39
  • @Lucero, with `bigint` type the cache size of `IDENTITY` is 10,000, not 1,000,000. So, this jump should be caused by something different. – Vladimir Baranov Oct 20 '16 at 11:42
  • The cache size may not be at the default - maybe a `SELECT * FROM sys.sequences` could shed some light into whether this was the issue or not. – Lucero Oct 20 '16 at 11:50
  • As for running out of IDs... you can do this 9'223'372'036 times until you run out of IDs (`bigint` range is 2^63 = 9'223'372'036'854'775'808). Not an issue I think. – Lucero Oct 20 '16 at 12:02
  • Thank you for the responses. @Lucero I've tried running this and no results come back. – user1024416 Oct 20 '16 at 13:29
  • It's the large jump that concerns me. – user1024416 Oct 20 '16 at 13:30

0 Answers0