SQL Server – Identity Value Jumps When Restarting

identitysql serversql-server-2012

We just switched from SQL Server 2008 R2 to SQL Server 2012. I am facing a problem with identity columns:

Whenever I restart SQL Server, the seed value for each identity column is increased by 1000 (for int identity columns it is 1000 and for bigint it is 10,000). For example, if the next int identity value for a table was 3, after restarting SQL Server it will be 1003. If I again restart SQL Server, it will be 2003 and so on.

After searching Google, I found that it is a new feature (don't know what is use of it) in SQL Server 2012, having only two solutions if you want the old identity behaviour:

  1. Use a sequence object

    This is not possible for me because:

    a) I am using the same database in SQL Server 2008 and 2012. I can't use sequence in 2008.

    b) If I go with sequence then I need to change the save procedure for each table, which would be a bulky task for us.

  2. Use Trace Flag 272 (-T272)

    I can go with this solution because there is no need to make any changes to my application. Someone suggested adding -T272 as a start-up parameter, after this SQL Server identity will work as in previous versions. I did the same but it is not working.

I don't want to make any changes to my database structure. Please suggest solutions or explain why -T272 is not working.

Best Answer

I found that it is a new feature (don't know what is use of it)

Before SQL Server 2012, identity allocations were always individually logged (as each value was used). This per-row logging activity could limit throughput in scenarios where many identity values are generated in a short space of time. To improve efficiency, SQL Server 2012 (and later) logs only the allocation of a batch of identity values. The allocated range is cached and issued on demand until a new batch of values is needed.

If SQL Server is restarted without the database containing the identity object being checkpointed on shut down, any remaining unused values in the cached range are lost, resulting in a jump in the values on restart.

Unfortunately, common ways of shutting down SQL Server 2012 do not currently automatically checkpoint databases (this contradicts the documentation, so it should be fixed at some point in the future). To avoid this particular cause of jumps in assigned identity values on SQL Server 2012, always shut down SQL Server using the T-SQL command SHUTDOWN (without the NOWAIT option).

The SHUTDOWN command will checkpoint all user databases correctly before shutting down the server. Do not use the Windows Service Control application, SQL Server Configuration Manager, the SQL Server Management Studio UI, or any other method.

You could also manually CHECKPOINT all databases before shutting down the SQL Server using any other method, but this requires you ensure no identity-allocation activity occurs in any database after your checkpoints, and before the shutdown completes. This may not be easy to achieve reliably.

More information and background in Lost Identity by Kalen Delaney.

Trace flag 272 is now documented in DBCC TRACEON - Trace Flags (Transact-SQL):

Disables identity pre-allocation to avoid gaps in the values of an identity column in cases where the server restarts unexpectedly or fails over to a secondary server. Note that identity caching is used to improve INSERT performance on tables with identity columns.

Note: Starting with SQL Server 2017, to accomplish this at the database level, see the IDENTITY_CACHE option in ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).

Scope: global only

Related Question