Web+Center Support
28 Oct

Knowledge Base Detail

Case Summary: Ticket case numbers and asset numbers index values jump up after database or server reboots for SQL*Server 2012
ID: 3104
Date Created: 01/27/2014
Last Modified: 01/27/2014
Problem Type: Problem Report
Item: Web+Center 7.5
Resolution:
This appears to be a critical bug in 2012 SQL*Server and their support for identity values in the database. If you read the article below, it appears Microsoft does not want to support the most simple identity scheme that has existed for decades that allow one to create sequential, index values for things like ticket numbers and asset numbers.

Previous, before SQL*Server 2012, set the field to be identity and index of 1 you could reliable create new indexes of

540
541
542
543
544
and so on.

Now with SQL*Server 2012 and the same database column definition it may look more like

540
541
(server reboot or database restarted happens)
..
1452
1453
1454

There is a work around in place for now:

You can use below two choices

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

Setting Trace Flag 272 on SQL Server 2012 that you are expecting here

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


A Web+Center user reported this error and we found this article on the web describing the general SQL*Server 2012 problem:

http://stackoverflow.com/questions/17587094/identity-column-value-suddenly-jumps-to-1001-in-sql-server

AND

http://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity

Return to FAQ List


Home | Search

Web+Center
Free and affordable Web-based Help Desk
Version: Ver. 9.0 - 02.07.17
(C) 2015 Internet Software Sciences
www.inet-sciences.com