Missing Identity Numbers

Question

Missing Identity Numbers

Frank Cazabon

Hall of Fame

Points: 3467
More actions
June 2, 2015 at 11:19 am

#317889

Hi,
I have inherited a system which uses Identity numbers for primary key fields, but also displays them for the user to see. The user has noticed missing numbers in the sequence, sometimes just a few numbers and sometimes as many as 1000 numbers missing.
As far as I can tell the few numbers missing could possibly be explained by users deleting records or the number being generated on a save, but the save then failing. But I can't explain the huge jump of 1000.
I can find no reference to the RESEED in the code.
Any ideas what could be causing this?

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 1

It sounds like you just explained it, users are deleting records or the application is trying to write a record and deciding to roll back the record, either directly in the DB or in the application by deleting the record and that got stuck in a loop in your application somewhere.

Is there an actual problem the users are experiencing besides why are there numbers missing?

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 2

Frank Cazabon (6/2/2015)
Hi,
I have inherited a system which uses Identity numbers for primary key fields, but also displays them for the user to see. The user has noticed missing numbers in the sequence, sometimes just a few numbers and sometimes as many as 1000 numbers missing.
As far as I can tell the few numbers missing could possibly be explained by users deleting records or the number being generated on a save, but the save then failing. But I can't explain the huge jump of 1000.
I can find no reference to the RESEED in the code.
Any ideas what could be causing this?

In simple terms, the sequence of the identities is not dense, a rollback or a delete would and will cause gaps. Wouldn't try to fix this, rather explain it to the client.

😎

Lowell SSC Guru Points: 323518 More actions · Answer 3

i belive a serverice stop and start of SQL automatically bumps identity seeds by 1000 in SQL2012

read about the feature / defect here:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3d256650-0e94-4d0f-8b52-0ba6e1903215/primary-key-auto-incrementing-by-1000-instead-of-1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

Lowell (6/2/2015)
i belive a serverice stop and start of SQL automatically bumps identity seeds by 1000 in SQL2012
read about the feature / defect here:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3d256650-0e94-4d0f-8b52-0ba6e1903215/primary-key-auto-incrementing-by-1000-instead-of-1

Well, that's an interesting one. Thanks, Lowell.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Michael L John One Orange Chip Points: 27054 More actions · Answer 5

Ed Wagner (6/2/2015)
Lowell (6/2/2015)
i belive a serverice stop and start of SQL automatically bumps identity seeds by 1000 in SQL2012
read about the feature / defect here:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3d256650-0e94-4d0f-8b52-0ba6e1903215/primary-key-auto-incrementing-by-1000-instead-of-1
Well, that's an interesting one. Thanks, Lowell.

Re-booting the server causes of 1000 in SQL 2012 and onward.

If there are single gaps, such as 100 to 102, keep in mind that an identity is incremented when an ATTEMPT to insert occurs.

So, if the insert throws an error, the identity will be incremented but it will not be stored in the table.

Run this code:

CREATE TABLE #Tmp

(

tmp_id int IDENTITY(1,1) NOT NULL,

tmp_val varchar(10) NOT NULL

)

INSERT INTO #Tmp (tmp_val)

VALUES ('Foo')

SELECT * FROM #tmp

INSERT INTO #Tmp (tmp_val)

VALUES ('Foo0000000')

SELECT * FROM #tmp

INSERT INTO #Tmp (tmp_val)

VALUES ('FooAgain')

SELECT * FROM #tmp

INSERT INTO #Tmp (tmp_val)

VALUES ('FooAgainAGAIN')

SELECT * FROM #tmp

INSERT INTO #Tmp (tmp_val)

VALUES ('Foo')

SELECT * FROM #tmp

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Frank Cazabon Hall of Fame Points: 3467 More actions · Answer 6

Thanks to all, especially the link to the bug when restarting the service.

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 7

Lowell (6/2/2015)
i belive a serverice stop and start of SQL automatically bumps identity seeds by 1000 in SQL2012
read about the feature / defect here:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3d256650-0e94-4d0f-8b52-0ba6e1903215/primary-key-auto-incrementing-by-1000-instead-of-1

Only seen this with AO, never on single instances

😎

Michael L John One Orange Chip Points: 27054 More actions · Answer 8

Eirikur Eiriksson (6/2/2015)
Lowell (6/2/2015)
i belive a serverice stop and start of SQL automatically bumps identity seeds by 1000 in SQL2012
read about the feature / defect here:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3d256650-0e94-4d0f-8b52-0ba6e1903215/primary-key-auto-incrementing-by-1000-instead-of-1
Only seen this with AO, never on single instances
😎

It happens on all of my single instances, for what that's worth.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 9

Michael L John (6/2/2015)
Eirikur Eiriksson (6/2/2015)
Lowell (6/2/2015)
i belive a serverice stop and start of SQL automatically bumps identity seeds by 1000 in SQL2012
read about the feature / defect here:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3d256650-0e94-4d0f-8b52-0ba6e1903215/primary-key-auto-incrementing-by-1000-instead-of-1
Only seen this with AO, never on single instances
😎
It happens on all of my single instances, for what that's worth.

Thanks for this Michael,

wondering what could be the difference here.

😎

Ed Wagner SSC Guru Points: 287026 More actions · Answer 10

Eirikur Eiriksson (6/2/2015)
Michael L John (6/2/2015)
Eirikur Eiriksson (6/2/2015)
Lowell (6/2/2015)
i belive a serverice stop and start of SQL automatically bumps identity seeds by 1000 in SQL2012
read about the feature / defect here:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3d256650-0e94-4d0f-8b52-0ba6e1903215/primary-key-auto-incrementing-by-1000-instead-of-1
Only seen this with AO, never on single instances
😎
It happens on all of my single instances, for what that's worth.
Thanks for this Michael,
wondering what could be the difference here.
😎

Service Packs applied, perhaps?

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 11

Ed Wagner (6/2/2015)
Eirikur Eiriksson (6/2/2015)
Michael L John (6/2/2015)
Eirikur Eiriksson (6/2/2015)
Lowell (6/2/2015)
i belive a serverice stop and start of SQL automatically bumps identity seeds by 1000 in SQL2012
read about the feature / defect here:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3d256650-0e94-4d0f-8b52-0ba6e1903215/primary-key-auto-incrementing-by-1000-instead-of-1
Only seen this with AO, never on single instances
😎
It happens on all of my single instances, for what that's worth.
Thanks for this Michael,
wondering what could be the difference here.
😎
Service Packs applied, perhaps?

Was just looking into that, some of the instances have gone through the whole update path without an issue, now on latest and "greatest" and still 😉 without the issue. The AOn's are consistently doing this though.

😎

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 12

Frank Cazabon (6/2/2015)
Thanks to all, especially the link to the bug when restarting the service.

It's actually not a "BUG". It's a performance feature. They preallocate 1,000 IDs to increase performance. I believe there's a trace flag that you can override that feature with but it's usually not a bother for me. This feature also affects SEQUENCEs.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Perry Whittle SSC Guru Points: 234013 More actions · Answer 13

it's not a bug it's by design and yes, there is a trace flag to force the engine to behave as sql server 2008 R2. The trace flag is 272, here is the Microsoft Connect item link

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

Jeff Moden (6/2/2015)
This feature also affects SEQUENCEs.

Not if sequence caching is turned off

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

j-1064772 SSCertifiable Points: 5316 More actions · Answer 14

[font="Comic Sans MS"]CRM 2011 uses sequential ID's to number invoices. Instead of merely relying on the next IDENTITY value, CRM calculates and stores in a table the next number available for the next invoice. This number is then retrieved when creating a new invoice.

Even that does not protect against gaps in the numbering sequence. Just by creating a new invoice, the next number is automatically increased in the table even if you do not save the new invoice. Duh!

When an auditor looks at your company books, you should at the very least be able to show which invoice numbers correspond to a real invoice and which numbers were simply wasted on failed attempts - the key point being that no invoice is actually lost, it is the *invoice-number* itself that corresponds to nothing.[/font]