Identity column value suddenly jumps to 1001 in sql server

Question

This question already has answers here:

Answer 1

Microsoft has changed the way they deal with identity values in SQL Server 2012 and as a result of this you can see identity gaps between your records after rebooting your SQL server instance or your server machine. There might be some other reasons for this id gaps, it may be due to automatic server restart after installing an update.

You can use below two choices

• Use trace flag 272 o This will cause a log record to be generated for each generated identity value. The performance of identity generation may be impacted by turning on this trace flag.

• Use a sequence generator with the NO CACHE setting

  Setting Trace Flag 272 on SQL Server 2012 that you are expecting here

• 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