This post is really just to help me find the necessary changes when I install SQL Server. But it could be useful to others who install SQL Server and then notice that their identity columns are jumping in blocks of 1000 (or similar).

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.

1. Open SQL Server Configuration Manager
2. Go to SQL Server Services
3. Double click SQL Server to open properties
4. Go to the Startup Parameters tab
5. Type -t272 and click Add
6. Click OK and then restart the service

The startup options are defined here.