Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Regarding sql server 2012 identity number gap problem after restarting sql server 2012 Expand / Collapse
Author
Message
Posted Monday, July 22, 2013 11:15 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 4:48 AM
Points: 47, Visits: 55
Hi friends,

I've a problem in sql server 2012, like i've one table, table name is "tblA".
In that Identity field Name is ID bigint not null identity[1,1].
Now i'm making few records entry like from 1 to 5.
Then i've close the sql server 2012. Then i've restarting the system.
Again i'm making another record entry. Here it should come ID value is 6.
But it has come like
10001.
Then again u make entry its coming correct 10002, 10003,10004 etc...
But i need when u restart the system it should continue with existing example
ID value 5+1=6, should start from 6 but its coming 10001...
Plz help why its coming like this.



Post #1476346
Posted Tuesday, July 23, 2013 1:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:44 AM
Points: 12,243, Visits: 9,215
pksutha (7/22/2013)
Hi friends,

I've a problem in sql server 2012, like i've one table, table name is "tblA".
In that Identity field Name is ID bigint not null identity[1,1].
Now i'm making few records entry like from 1 to 5.
Then i've close the sql server 2012. Then i've restarting the system.
Again i'm making another record entry. Here it should come ID value is 6.
But it has come like
10001.
Then again u make entry its coming correct 10002, 10003,10004 etc...
But i need when u restart the system it should continue with existing example
ID value 5+1=6, should start from 6 but its coming 10001...
Plz help why its coming like this.


The IDENTITY property doesn't give any garantuee there will be no gaps.
SQL Server 2012 caches some of the next identities that might be inserted, in order to speed up the performance. If you restart the server, this cache will be lost.

Maybe you can try to take a look at sequences, if avoiding gaps is important.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1476374
Posted Tuesday, July 23, 2013 1:34 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 2:55 PM
Points: 41,570, Visits: 34,495
Koen Verbeeck (7/23/2013)
Maybe you can try to take a look at sequences, if avoiding gaps is important.


Sequences have the same gap-causing properties as identities (identities in SQL 2012 use the Sequence code)




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1476389
Posted Tuesday, July 23, 2013 8:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:42 PM
Points: 14,840, Visits: 27,315
This is a known behavior. I reported it as a bug a while back. Microsoft has supplied a work around. You can enable TraceFlag 272 on your server. There are other workarounds as well. Check out the Connect entry for more information.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1476628
Posted Wednesday, July 24, 2013 3:56 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 7:54 AM
Points: 758, Visits: 633
pksutha (7/22/2013)
Again i'm making another record entry. Here it should come ID value is 6.


No. It should become a value > 5.

If you need consecutive numbers, you should not use IDENTITY. The point with IDENTITY and sequences is that they are non-blocking. That is, one process can get a number although another process that just took a number has not yet committed. This also means that there can be gaps if there is a rollback. And in some cases there can be bigger gaps. Which is perfectly OK, and it is completely beyond me why Microsoft wasted time on this trace flag.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1477307
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse