When you restart Microsoft SQL Server 2012 or a later version of SQL Server, IDENTITY or SEQUENCE values are reseeded to a greater range.
For example, if you have an integer data type and your last IDENTITY value is 7, the value jumps into the 1000 range after SQL Server restarts.
This issue occurs because of pre-allocation for identity values.
About pre-allocation for identity values
To improve the performance for high-end systems, Microsoft SQL Server 2012 introduced pre-allocation for identity values. If a SQL Server shutdown occurs and a checkpoint can't be issued against the database because of database locks against it or because the shutdown is unexpected, current IDENTITY and SEQUENCE values can't be written to the database. In this case, when SQL Server starts back up, it starts to use values from the pre-allocated range.
To fix this problem, set trace flag -T3597 as a startup parameter in SQL Server.
- Setting -T3597 as a startup parameter enables a new functionality that was added in SQL Server 2014. The functionality makes sure that a checkpoint for IDENTITY and SEQUENCE values occurs even if a full database checkpoint isn't possible. This functionally was added under trace flag 3597. When you issue a SQL Server service shutdown and this trace flag is enabled, it minimizes or eliminates the possibility of re-seeding IDENTITY values from a greater range.
- Be aware that this trace flag isn’t helpful for an unexpected shutdown of SQL Server, such as because of a power outage or termination of the SQL Server process. This is because no guarantee can be provided that a checkpoint of the identity values has occurred.
The SQL Server 2012 performance-boosting functionality that pre-allocates identity values can be disabled by using T 272. This trace flag reverts to the behavior of Microsoft SQL Server 2008 R2 and older versions.
Note IDENTITY columns don't necessarily guarantee sequential numbering. However, they do provide uniqueness.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Article ID: 4038454 - Last Review: 2017, ഓഗ 13 - Revision: 14