SQL Server identity values jump by 1000 when it is restarted

Have you ever run into the issue when the SQL server got restarted and suddenly all the Auto Identity values jumped by 1000? Recently a client asked to prevent this issue as their IDs jumped unknowingly. Here is how it can be fixed.

Issue:

If you are using surrogate keys or primary keys in the SQL tables that are set to auto increment, you may notice this issue in production. MS SQL Server, when restarted, auto increments those values by 1000. So if a ID column value was 10112 the next record that gets added after restart will get ID value 11112.

Here is a thread on this issue: MSDN

Thread Resolution (for prevention):

This is indeed a feature of later version of SQL Server. It "caches" 1000 identity values in advance for performance reasons (more on that later).

This can be disabled with a couple of methods.If your data already fell victim to this then you would have to go back and update those records. That's a whole another resolution. But to prevent this from happening in future, here is a resolution.

The "272" trace flag enables logging of identity computations and apparently disabled this behavior, essentially reverting to the older SQL server behavior.

Microsoft warns that this might carry a performance penalty, but it shouldn't be significant.

• Open "SQL Server Configuration Manager"
• Click "SQL Server Services" on the left pane
• Right-click on your SQL Server instance name on the right pane ->Default: SQL Server(MSSQLSERVER)
• Click "Properties"
• Click "Startup Parameters"
• On the "specify a startup parameter" textbox type "-T272"
• Click "Add" Confirm the changes Hope this works for you.

Let us know if you found a different resolution or any issues with above resolution.