SQL Identity Column Jumping
Starting with SQL Server 2012, Microsoft implemented identity columns with caching. It is documented here. The net effect is that after a server reset your identity IDs may be much larger than you expected because of this. To restore the old behavior you need to add a parameter to SQL Server. Normally you could argue that the IDs shouldn’t matter and I completely agree. But having values jump in increments of 1000 could cause you to run out of room faster than normal if you’re using something other than BIGINT values.
- Open SQL Server Configuration Manager
- Go to SQL Server Services
- Double click SQL Server to open properties
- Go to the Startup Parameters tab
- Type -t272 and click Add
- Click OK and then restart the service
The startup options are defined here.