identity jumping from default to bigger large number


Overview of the issue:

It is small web based productions application and hosted in SQL express 2012 for the license costing.

Issue is whenever SQL restarted for whatever reason identity numbers are changed very bigger and identity jumping from one to other digits.

To fix this in our case we cannot do this at database level or table schema script level to NOCACHE or disable the IDENTITY CACHE, since it is been enabled many databases and application code changes are not advised.


Enable trace flag T272 at instance level in the start up parameters.


I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

Your email address will not be published. Required fields are marked *

9 + 1 =