Friday, May 20, 2016

Identity increment is jumping in SQL Server database

Please look at the below screenshot to understand what jumping of identity value is (It is taken from SQL 2014 Database table.)

Observe records below the red line..the identity value jumped from 17 to 1014 and started continuing from there.

Cause: This is not actually the issue or bug. It is by design in SQL versions from 2012
SQL Server was assigning values to identity column individually like by adding 1 to the existing ID till SQL 2012 versions. From SQL 2012 the SQL server allocating a batch of identity values like 1,000 or 10,000 numbers to the cache to speed up the process.

When ID Jump happens: when unexpected SQL services or server restart happens.

Why: There can be many reasons but mainly when SQL Server is restarted without the database containing the identity object being check pointed on shut down, any remaining unused values in the cached range are lost, this caused the jumping.

   1)      Creating Sequences to the identity columns. Click 

   2)      Enable Trace Flag 272 in startup parameters.
Ø  Open SQL Server Configuration Manager.
Ø  Right-click SQL Server and select Properties.
Ø  In the opening window under Startup Parameters, type -T272 and click Add, then press Apply button and restart SQL Services.

Note: Shutting down SQL services in proper way prevents this scenario. shudown


  1. This was an identified issue in Sql Server 2012 HADR that was fixed in a CU on SP2. You may want to check into something similar on Sql Server 2014.

  2. This comment has been removed by the author.