How to solve identity problem in SQL 2012

Microsoft changed the Identity behavior to jump in increment of 1000 or 10000 whenever the SQL Server 2012 restart. For example: we have a Primary Key that was 10012, if you re-start the SQL server instant, then in the next Insert record you will get the Primary Key as 11012.

Call us for Free Consultation for Remote DBA services at our SQL Consulting Firm at: 732-536-4765

If the Primary Key field is an Integer (int) data type then the increment value is 1000. But for a big Integer (bigint) data type then the value will jump 10000. That kind of behavior is not acceptable and a solution must be found right away and not to mention the problem it can cause if you use this value in a contract number or an invoice.

We found that out in the hard way when Windows Server 2012 had an update at 3:00 AM and the virtual machine re stared and consequently MS SQL server 2012 re-started as well. Suddenly the Customer Primary Key showed us a number which was a none-sequential and which was jumped in increment of 1000.

This is basically can be considered as a new feature which was introduced by Microsoft and started with SQL 2012.

We found out that if we use trace flag 272 then it will cause a log record to be generated for each generated identity value. But the performance of identity generation may be impacted by turning on this trace flag.

We implemented the trace flag 272 and we did not see any degradation in performance what so ever. Implementing the trace flag 272 will make MS SQL 2012 to work as it was working in SQL server 2008 or older versions.

In addition, for SQL 2012 Microsoft created a Sequence generator with NO CHACHE that generates a sequence of numeric values see this link for more info at: https://msdn.microsoft.com/en-us/library/ff878091.aspx

Here are the steps to resolve the Identity problem:

1. Open up the SQL Server Configuration Manager

2. Click the left side of the SQL services in the below image.

Check our Custom Software Development Services.

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)

4. Click the Properties option as shown in the above image.

5. Click the Startup Parameters tab as shown the below image.

6. Type the -T272 in the Specify a startup parameter Textbox field.

7. Click the Add button as shown in the above image. You should see the –T272 parameter within the Existing parameters section in the below image.

8. Click the Apply button and you will get the below Warning.

9. OK the below Warring message and Restart the SQL Server instant service.