By kashif


2013-01-03 19:52:31 8 Comments

In one of my tables Fee in column "ReceiptNo" in SQL Server 2012 database identity increment suddenly started jumping to 100s instead of 1 depending on the following two things.

  1. if it is 1205446 it is jumps to 1206306, if it is 1206321, it jumps to 1207306 and if it is 1207314, it jumps to 1208306. What I want to make you note is that the last three digits remain constant i.e 306 whenever the jumping occurs as shown in the following picture.

  2. this problem occurs when I restart my computer

enter image description here

6 comments

@Jeyara 2015-03-30 23:31:21

I know my answer might be late to the party. But i have solved in another way by adding a start up stored procedure in SQL Server 2012.

Create a following stored procedure in master DB.

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ResetTableNameIdentityAfterRestart]
AS
BEGIN

begin TRAN
    declare @id int = 0
    SELECT @id =  MAX(id) FROM [DatabaseName].dbo.[TableName]
    --print @id
    DBCC CHECKIDENT ('[DatabaseName].dbo.[TableName]', reseed, @id)
Commit

END

Then add it in to Start up by using following syntax.

EXEC sp_procoption 'ResetOrderIdentityAfterRestart', 'startup', 'on';

This is a good idea if you have few tables. but if you have to do for many tables, this method still works but not a good idea.

@Martin Smith 2013-01-03 19:57:08

You are probably encountering the issue here (wayback machine).

SQL Server 2012 now uses a cache size of 1,000 when allocating IDENTITY values in an int column and restarting the service can "lose" unused values (The cache size is 10,000 for bigint/numeric).

From the data you have shown it looks like this happened after the data entry for 22 December then when it restarted SQL Server reserved the values 1206306 - 1207305. After data entry for 24 - 25 December was done another restart and SQL Server reserved the next range 1207306 - 1208305 visible in the entries for the 28th.

Unless you are restarting the service with unusual frequency any "lost" values are unlikely to make any significant dent in the range of values allowed by the datatype so the best policy is not to worry about it.

If this is for some reason a real issue for you see the workarounds on the linked Connect Item thread.

  1. You can use a SEQUENCE instead of an identity column and define a smaller cache size for example and use NEXT VALUE FOR in a column default.
  2. Or apply trace flag 272 which makes the IDENTITY allocation logged as in previous versions.

You should be aware neither of these workarounds assure no gaps. This has never been guaranteed by IDENTITY as it would only be possible by serializing inserts to the table. If you need a gapless column you will need to use a different solution than either IDENTITY or SEQUENCE

@kashif 2013-01-03 21:22:23

to verify what you said I inserted some values and got 1208309, 1208310 and then I restarted the server and then when I added the row I got 1209309 that means what you said is absolutely right. thanks alot. now can you plz tell me how can I solve this problem. would you suggest me to use sql server 2008 instead of 2012 that I was previously using or even using 2012 this problem can be resolved??

@Martin Smith 2013-01-03 21:26:22

@kashif - Is it actually a problem for you? Even if you use up 1,000 identity values a day it will still take 2 million days before you run out of values. If you do want the old behaviour you can set SQL Server to startup with trace flag 272 or you can use a SEQUENCE instead of an IDENTITY and set the Sequence to have a cache size of 0.

@kashif 2013-01-03 21:42:18

I got Quite Impressive and satisfactory answers from you for my solution thanks alot.

@Martin Smith 2013-01-04 17:35:43

Actually from your CREATE TABLE I see you are using numeric(7) and have started the numbering off at 1200001 that means you would run out after 8,799 days (24 years) if you use 1,000 per day.

@StarPilot 2014-03-12 17:57:32

The actual value "jumped" is supposed to be dependent on the column type used. For instance, a big int column will usually "jump" by 10,000 per restart.

@Justin Skiles 2015-08-12 12:42:09

I've experienced this problem without any restart of the instance.

@Chris Nevill 2016-02-08 16:50:41

I was running a debug instance of an application we have out in the wild. I had this happen and I almost had a heart attack thinking I'd somehow inadvertently been working on the live server! Thanks for this. Panic over :)

@stom 2016-03-05 15:50:59

check here the issue is reproduced to know what exactly is happening.

@Steve 2018-03-08 21:12:56

It's not really a problem, it just looks weird. I'm seeing it LocalDB. Small table, so I have 1, 2, 3, 4, 5, 1004, 1005.

@Lukasz Szozda 2017-04-19 19:39:32

From SQL Server 2017+ you could use ALTER DATABASE SCOPED CONFIGURATION:

IDENTITY_CACHE = { ON | OFF }

Enables or disables identity cache at the database level. The default is ON. Identity caching is used to improve INSERT performance on tables with Identity columns. To avoid gaps in the values of the Identity column in cases where the server restarts unexpectedly or fails over to a secondary server, disable the IDENTITY_CACHE option. This option is similar to the existing SQL Server Trace Flag 272, except that it can be set at the database level rather than only at the server level.

(...)

G. Set IDENTITY_CACHE

This example disables the identity cache.

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF ;

@green_mystic_Orb 2017-03-07 14:06:11

This is still a very common issue among many developers and applications regardless of size.

Unfortunately the suggestions above do not fix all scenarios, i.e. Shared hosting, you cannot rely on your host to set the -t272 startup parameter.

Also, if you have existing tables that use these identity columns for primary keys, it is a HUGE effort to drop those columns and recreate new ones to use the BS sequence workaround. The Sequence workaround is only good if you are designing the tables new from scratch in SQL 2012+

Bottom line is, if you are on Sql Server 2008R2, then STAY ON IT. Seriously, stay on it. Until Microsoft admits that they introduced a HUGE bug, which is still there even in Sql Server 2016, then we should not upgrade until they own it and FIX IT.

Microsoft straight up introduced a breaking change, i.e. they broke a working API that no longer works as designed, due to the fact that their system forgets their current identity on a restart. Cache or no cache, this is unacceptable, and the Microsoft developer by the name of Bryan needs to own it, instead of tell the world that it is "by design" and a "feature". Sure, the caching is a feature, but losing track of what the next identity should be, IS NOT A FEATURE. It's a fricken BUG!!!

I will share the workaround that I used, because My DB's are on Shared Hosting servers, also, I am not dropping and recreating my Primary Key columns, that would be a huge PITA.

Instead, this is my shameful hack (but not as shameful as this POS bug that microsoft has introduced).

Hack/Fix:

Before your insert commands, just reseed your identity before each insert. This fix is only recommended if you don't have admin control over your Sql Server instance, otherwise I suggest reseeding on restart of server.

declare @newId int -- where int is the datatype of your PKey or Id column
select @newId = max(YourBuggedIdColumn) from YOUR_TABLE_NAME
DBCC CheckIdent('YOUR_TABLE_NAME', RESEED, @newId)

Just those 3 lines immediately before your insert, and you should be good to go. It really won't affect performance that much, i.e. it will be unnoticeable.

Goodluck.

@Harun ERGUL 2014-10-03 21:02:57

This problems occurs after restarting the SQL Server.

The solution is:

  • Run SQL Server Configuration Manager.

  • Select SQL Server Services.

    SQL Server Configuration Manager

  • Right-click SQL Server and select Properties.

  • In the opening window under Startup Parameters, type -T272 and click Add, then press Apply button and restart.

    SQL Server startup parameters

@Fragment 2015-02-13 07:19:41

This method is really work, thanks a lot! and as told here, this issue won't be fixed in SQL Server 2012, and in it's service packs, - only in next version release.

@Ege Ersoz 2015-07-07 16:41:00

Is there a way to apply the trace flag to individual databases? I don't want to make this change on the whole server because I have third-party databases and I'm not sure how this will affect them.

@Savage 2016-03-17 09:53:55

I didn't follow the reasons, but apparently some users had to use lower case "t" to get it to work. See the link posted by Fragment in the comment above.

@Ian Boyd 2019-03-29 14:35:59

Fragment's link archive

@Sebastian Meine 2013-01-03 19:59:50

There are many possible reasons for jumping identity values. They range from rolled back inserts to identity management for replication. What is causing this in your case I can't tell without spending some time in your system.

You should know however, that in no case you can assume an identity column to be contiguos. There are just too many things that can cause gaps.

You can find a little more information about this here: http://sqlity.net/en/792/the-gap-in-the-identity-value-sequence/

Related Questions

Sponsored Content

8 Answered Questions

[SOLVED] How does database indexing work?

42 Answered Questions

[SOLVED] How to return only the Date from a SQL Server DateTime datatype

45 Answered Questions

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

25 Answered Questions

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

3 Answered Questions

[SOLVED] Identity column value suddenly jumps to 1001 in sql server

4 Answered Questions

[SOLVED] Inserting multiple rows in a single SQL query?

15 Answered Questions

[SOLVED] How to Delete using INNER JOIN with SQL Server?

37 Answered Questions

Sponsored Content