Significant issue with SQL*Server 2012 effecting Web+Center and other DB apps

SQL*Server 2012 does not properly support basic identity/auto increment of case numbers or identity database types.

The Problem:

Case # 103, 104, 105, 106, 1345, 1346, 1347

Do you see a problem?
All you did was reboot the server!

An issue reported by a Web+Center client this morning who reported that his case numbers and assets numbers seem to randomly jump up by + 1000 and then continue for a week or so before doing it again had us baffled for quite a while.  We did a quick Google search and found many articles about the same issue with identity values which jump suddenly after a system reboot or a database restart with SQL 2012.

When Web+Center is set up, each new case number should increase by 1.  With SQL*Server 2012, if you happen to do a system reboot or a database restart,  SQL*Server may reset the identity seed which should be the previous highest value for that database column to something 1000 or even 10,000 higher.  When this happens, you will have big gaps in the case numbers, asset numbers and other indexes.  Although the Web+Center product will continue to work fine with these big gaps in case numbers or asset numbers, we always planned for the identity field to work as expected by defining the identity values, starting seed and index increment of 1.

More importantly, you may have other applications installed on your 2012 DB server over that do depend upon the identity field working exactly with the original specification and there will certainly be some applications that will not work properly under SQL 2012 that work fine with SQL 2008.  IF you have other applications running under SQL 2012, they may be creating indexes differently causing some issues that seem random but knowing this issue that occurs after system reboots or database restarts, it may help solve some difficult database application debugging issues.

Known workarounds:
There are currently two workarounds:

You can use below two choices:

  • Use trace flag 272  This will cause a log record to be generated for each generated identity value. The performance of identity generation may be impacted by turning on this trace flag.
  • Use a sequence generator with the NO CACHE settingSteps for Setting Trace Flag 272 on SQL Server 2012
  • Open “SQL Server Configuration Manager”
  • Click “SQL Server Services” on the left pane
  • Right-click on your SQL Server instance name on the right pane ->Default: SQL Server(MSSQLSERVER)
  • Click “Properties”
  • Click “Startup Parameters”
  • On the “specify a startup parameter” textbox type “-t272”
  • Click “Add”
  • Confirm the changes

Microsoft Not Ready to fix this problem

This issue has been around now for over a year (since 2012) with a lot of discussions about it on various SQL and Microsoft support discussion groups.  If you review some of the related discussions related to this issue you can see that Microsoft does not seem ready to fix this issue with a patch or upgrade.

Here is a posting from 12/10/2012 from Microsoft concerning this issue and why it changed this identity specification:

Posted by Microsoft on 12/10/2012 at 10:00 AM
Hello,
First off we do apologize for the late response to this issue.In SQL Server 2012 the implementation of the identity property has been changed to accommodate investments into other features. In previous versions of SQL Server the tracking of identity generation relied on transaction log records for each identity value generated. In SQL Server 2012 we generate identity values in batches and log only the max value of the batch. This reduces the amount and frequency of information written to the transaction log improving insert scalability.

If you require the same identity generation semantics as previous versions of SQL Server there are two options available:
•         Use trace flag 272
o This will cause a log record to be generated for each generated identity value. The performance of identity generation may be impacted by turning on this trace flag.
•         Use a sequence generator with the NO CACHE setting(http://msdn.microsoft.com/en-us/library/ff878091.aspx)
o This will cause a log record to be generated for each generated sequence value. Note that the performance of sequence value generation may be impacted by using NO CACHE.
o Example:
 CREATE SEQUENCE s1 AS INT START WITH 1 NO CACHE;
CREATE TABLE t1 (Id INT PRIMARY KEY DEFAULT NEXT VALUE FOR s1, col INT NOT NULL);

As documented in books online for previous versions of SQL Server the identity property does not guarantee the absence of gaps, this statement remains true for the above workarounds. These solutions do help with removing the gaps that occur as part of restarting the instance in SQL Server 2012.

Best regards
Vishal

Other discussion groups links – Here are some discussion groups threads that may be of interest to you if you are running SQL 2012 or plan to upgrade to SQL 2012 from SQL 2008 anytime soon.

If you find additional information about the identity value changes for SQL 2012, please contact us so we can inform our Web+Center users of other changes or workarounds.  Although we don’t use the case number value when calculating reports, case numbers have always offered a very good estimate of the number of new, or total cases.  For this reason, we recommend Web+Center users who are running on SQL 2012 to make the trace flag modification to prevent gaps in your case and asset numbers.  Feel free to forward this email on to fellow IT database peers as a heads up!