Post Installation Steps (to fix sql-server-identity-jumping-1000-identity_cache)

This article is a step by step guide to perform post installation SQL Server steps

Applies To

This article applies to all SQL Server versions

Issue

There is issue noticed in SQL database with table identities that are jumping values.

10,000 Jump:

Due to this, in our sample scenario 6585 rows are taking 26879 primary key values:

This is an issue with SQL Server  and similar issue is explained on link below:

https://blog.sqlauthority.com/2018/01/24/sql-server-identity-jumping-1000-identity_cache/

Fix

Please login as SA or Db Admin, select MachPanel_DB (or whatever the DB name) for your installation

Scenario A:  SQL Server version 2017 or Later:
Run below command to turn Identity Cache to Off.

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF;
GO

Scenario B:  SQL Server versions Earlier than SQL Server 2017:
Perform following steps

1. Open up the SQL Server Configuration Manager
2. Click the left side of the SQL services in the below image.
3. Right click on the SQL Server instant name at the right pane as shown in the above image. Default value is: SQL Server (MSSQLSERVER),
   Click the Properties option as shown in the above image.
4. Click the Startup Parameters tab as shown the below image.
5. Type -T272 in the Specify a startup parameter Textbox field and click Add
   
   
   
6. –T272 parameter will appear in the Existing parameters section as shown in below image.
   
7. Click the Apply button and you will get the below Warning.
8. Click OK, as Warring message pops up, and Restart the SQL Server instant service.