In one of our clients' (testers') databases the Identity column of a SQL Server Local Db datatable looks thus:

enter image description here

So, most of the time the identity increases by one, but sometimes the identity jumps up by 10,000. Possibly this coincides with moving the database between computers and reattaching them to SQL Server Local Db.

What may be related is the fact that this is an Entity Framework Code First generated database with Migrations. At one time there was a migration that executed the following Transact SQL: Sql("DBCC CHECKIDENT (Sessions, RESEED, 10000)"), This database may have seen this migration. However, I deleted the migration afterwards and the _MigrationsHistory table in the database does not have a record for this particular migration.

If I have a look at the design for the table it specifies an identity of (1,1).

So, does anyone have some inkling about what is going on? Can I stop the 10,000 jumps?

EDIT: The reseed had nothing to do with it. As indeed can be read here the jump by 10,000 is a feature of SQL server for identity columns of type big int (64 bit).

Why I bother? The SQL Server Localdb engine is connected to a legacy VB6 application that we are migrating to .Net. The VB6 code is geared towards using 16 bit identity fields. Jumps of 10,000 at a time are disastrous...

  • 1
    What version of sql server you are using - 2012, 2014 ? And what edition ? Is this happening randomly or after restarts ?
    – Kin Shah
    Oct 26 '15 at 23:14
  • reattaching them to SQL Server Local Db. -->Also, why are you using detach/attach method as opposed to backup / restore ? Have you looked into Trace Flag 272 ?
    – Kin Shah
    Oct 26 '15 at 23:20
  • @Kin We do use backup/testore. The sql server version is 2014
    – Dabblernl
    Oct 26 '15 at 23:23
  • Your question is confusing .. you are telling that a DBCC CHECKIDENT (Sessions, RESEED, 10000) was ran against Sessions and you deleted _MigrationsHistory table .. where are you seeing the identity increment problem ? I suppose it is sessions. So you have to reset your identity to the max identity + 1.
    – Kin Shah
    Oct 26 '15 at 23:29
  • 1
    Why do you care about gaps? Identity values should be meaningless, surrogate values. (Also, I recommend against TF 272 - yes it "solves" the "problem" but it does so by logging every single identity assignment. Works fine on your machine but not at scale.) Oct 27 '15 at 0:15

Browse other questions tagged or ask your own question.