Home Dashboard Directory Help
Search

Failover or Restart Results in Reseed of Identity by Grant Fritchey


Status: 

Closed
 as By Design Help for as By Design


201
3
Sign in
to vote
Type: Bug
ID: 739013
Opened: 4/26/2012 6:38:41 AM
Access Restriction: Public
5
Workaround(s)
view
86
User(s) can reproduce this bug

Description

When a table with less than 1000 rows that has an identity value is part of a database that is failed over in an AlwaysOn availability group, the identity is reseeded to 1000. If the identity value is already over 1000, no reseed occurs.

This also occurs if you restart the server.
Details
Sign in to post a comment.
Posted by DanDaMan00 on 7/4/2014 at 7:08 AM
Hi, I have just found out about this SEVERE BUG and it is unbelievable that Microsoft developers consider it as a "feature".
Have they put any thought into the consequences of this new implementation?
Have they realized that this feature would potentially reduce the range of an integer by a factor of 100 / 1000 / 10000 or so?
I have just tested and reproduced the bug with a standalone SQL 2012 SP1 Enterprise: I created a table with an smallint column configured as IDENTITY, entered 2 rows (resulting in ids 1 and 2), restarted the instance and inserted another row; the newly created Id is 102.
Now, considering that the range for a smallint is -32,767 to 32,767, this means that after ~327 restarts, one would run out of range !!! While this is a worst case scenario, it can happen that some computers are restarted daily, so in less than one year, someone could hit this BUG. And I am not even thinking about other unexpected scenarios that could result in the jump, without even restarting the SQL server ...

Microsoft, please wake up and listen to your customers and developers!!! THIS IS A SERIOUS BUG, NOT a FEATURE. And it should be handled as a bug and fixed, not provided workarounds for. Eventually, using this "feature" should be enabled with traceflags or whatever, but the default behavior should be as it has been up to SQL 2008 R2
Posted by James079 on 6/17/2014 at 5:17 AM
Can you please explain what are the "benefits" of this new design of jumping by a 1000 every time SQL server is restarted ?
Posted by ebooyens on 6/6/2014 at 1:15 AM
This is shocking, I added in flag T272 and it still jumps, what the hell?? PLEASE just explain yourself Microsoft and offer a solution!!
Posted by nikhilzkingdom on 5/27/2014 at 9:34 PM
If the issue is closed as "By Design". I'm still waiting to find the rationale behind this being "as designed".

Please post an explanation
Posted by gregslater on 5/17/2014 at 5:35 PM
I'm experiencing this issue with SQL Azure. I can't find any workarounds for it that I can apply when using Azure.

The identity value jumps by 10,000 every few weeks. This needs fixing quickly, before my Id column overflows!
Posted by ChrisAVWood on 4/24/2014 at 8:37 AM
We are starting to see this and are pretty late on it as we installed SQL2012 late last year.
I am wondering if in the last Microsoft comment - dated 5/6/2013 a mention was made about the control manager shutdown.
'For control manager shutdown, we have a fix for next verion (with another TF). This fix will take care of most control manager shutdown cases.'
Has this been implemented in a SP1 CU and what TF is it?

Thanks

Chris
Posted by CodingBullets on 3/14/2014 at 12:47 PM
This is a VERY serious problem.. and it needs to be addressed by the Dev team.

Do you really believe that users are going to be "OK" with a huge Identity seed jump?

I have 60-90 days before a major rollout here.. and now I'm faced with using these workarounds to solve something that shouldn't even exist.

Not only that.. the issue has been open since 2012. I mean.. with that kind of timeframe.. I have to ask, is this how developers who use your platform end up getting treated as customers?
Posted by MParry789 on 3/13/2014 at 1:53 PM
I also experienced this issue using Enterprise Edition, build 11.0.3373.0. A manual fail over was performed using the command "ALTER AVAILABILITY GROUP myGroup FAILOVER" without doing a checkpoint first. The ID value in one table jumped by 1,000.
Posted by Dmitry Kirsanov on 3/4/2014 at 1:36 AM
I could also add, that this problem affects Windows Azure database, and description of the bug is incorrect - it doesn't matter how many records you have and if the identity value is over 1000 - I've seen this issue on tables large and small. There is no workaround for Azure.

I agree that this is a bug of high severity.
Posted by SQLServerSteve on 1/28/2014 at 4:11 PM
I apologize for posting three times in one day, but I ought to add that as soon as I signed out of this forum, I saw the identity values in one table I've been having trouble with all day jump forward by four. This was with the trace flag set, so either the fix mentioned here doesn't work, or is only applicable in certain cases. Keep in mind that this identity problem waxes and wanes for unknown reasons; I've gone months without seeing a single odd identity value, only to see it suddenly reappear out of the blue, like today, when I've seen more than a dozen such jumps on one table. This means that various fixes might appear to work for some users for a time, when in fact they might not be working at all, which is a good reason to keep this thread open. For example, I assumed that the bug had been taken care of by Service Pack 1 because it vanished for so long, but I was apparently wrong. I think a lot more diligence is needed to solve one of the most serious bugs in SQL Server's history.
Posted by SQLServerSteve on 1/28/2014 at 3:40 PM
I'd also like to add that the current description of the problem needs to be changed because each part of it is incorrect: "When a table with less than 1000 rows that has an identity value is part of a database that is failed over in an AlwaysOn availability group, the identity is reseeded to 1000. If the identity value is already over 1000, no reseed occurs." None of this is true. The jumps occur in tables with greater or fewer than 1,000 rows, in databases that aren't part of AlwaysOn availability groups, and are reseeded in increments of 10, 100, 1,000, 10,000, or 4. If the identity value is already over 1,000, reseeds may still occur - including in a live environment, with no checkpoints, failovers, restarts or other such conditions. A more accurate description might be, "The identity value of any table may be reseeded without warning in increments of 4, 10, 100, 1,000 or 10,000, no matter what the original value was. This may occur between server restarts, failovers and log checkpoints, regardless of whether the server is a member of an Availability Group or not."
Posted by SQLServerSteve on 1/28/2014 at 3:31 PM
No, please don't close this as "Won't Fix (by design)" yet.

First of all, some of us have yet to verify that this trace flag actually works in practice; the first time I tried it, the identity values on three recently updated columns suddenly jumped by 10,000. Before applying the trace flag, the identity values on one particular column had been leaping forward in increments of four all day long, which brings me to point #2: the dimensions of the problem are greater than what's been discussed so far in this thread. Over the last year and a half, I've also seen values jump in multiples of four, albeit not as frequently as the multiples of base 10 that are commonly reported. I've seen these jumps occur in a live environment many times, in the absence of any checkpoint, failover, server restart, or other cause mentioned to date in this thread.

Thirdly, this behavior ought to be changed because it is simply very, very bad design. In exchange for a minor performance enhancement, Microsoft has introduced the most serious threat to data quality in the history of SQL Server. Given that the primary job of database servers is to ensure data integrity, not return incorrect results at a high speed, this "feature" ought to be turned off by default. It is only a slight improvement for a small number of use cases, but introduces a major problem for a large majority of users and amounts to a breaking change for applications that depend on the old behavior. The feature should be off by default and the trace flag ought to be changed ASAP so that it turns it on for the few users who need it. By leaving the matter as it stands, the matter is for all intents and purposes being closed as, "Won't fix (because we don't care about end users or the quality of our product)." The bottom line is that identity generation used to be more reliable in the product by several orders of magnitude, but now it is not, and this is not what end users want.
Posted by aqx on 1/13/2014 at 2:39 PM
So it should be closed as Won't fix (by design). Please do this.
Posted by Tharindu Dhaneenja on 10/8/2013 at 3:06 AM
another issue we have this Identity columns (hope this will help some peoples) ,
http://databaseusergroup.blogspot.com/2013/10/deadlocked-on-sql-server.html
Posted by Yogesh Ranade on 8/2/2013 at 11:39 AM
got response from MS that T272 will be available atleast next couple of versions (2014 etc) and identity jumping feature will stay as is in 2012
Posted by Yogesh Ranade on 7/25/2013 at 11:07 AM
I don't have SP1. Let me try with SP1 and see if they changed it from T to t.
Posted by SteveBouff on 7/19/2013 at 7:45 AM
"-T272 is working just fine for me. More importantly is the -T272 is going to get deprecated???? Microsoft please respond. "

Now that is strange behavior. I know of another DBA where -T272 didn't work, so I asked him to try lower case -t272 and it worked.

Running SP1
Posted by Yogesh Ranade on 7/18/2013 at 9:28 PM
-T272 is working just fine for me. More importantly is the -T272 is going to get deprecated???? Microsoft please respond.
Posted by SteveBouff on 7/17/2013 at 8:52 AM
The trace flag comments are not entirely correct. I tested this as stated below, however it will NOT work as indicated below unless you use the lower case t, as the prefix for the parameter.

6. On the "specify a startup parameter" textbox type "-T272"

should be

6. On the "specify a startup parameter" textbox type "-t272" (little t denotes a MS internal trace flag)
A lowercase "t" is accepted by SQL Server, but this sets other internal trace flags that are required only by SQL Server support engineers.
http://technet.microsoft.com/en-us/library/ms190737(v=sql.110).aspx


Then it will work properly.

Steve
Posted by Liam Dwan on 7/9/2013 at 3:29 AM
I had this happen to me on Developer Edition just now - except the jump was 10,000. I went from 793 to 10793.
Posted by Yogesh Ranade on 6/28/2013 at 10:46 AM
Can someone provide information about TF -T272 being deprecated?
Posted by vishalishere on 6/18/2013 at 4:40 PM
http://social.msdn.microsoft.com/Forums/windowsazure/en-US/home?category=sqlserverdataservices
Posted by vishalishere on 6/18/2013 at 4:40 PM
for those who want to know how to add Trace flag 272 here are the steps

1. Open "SQL Server Configuration Manager"
2. Click "SQL Server Services" on the left pane
3. Right-click on your SQL Server instance name on the right pane
4. Click "Properties"
5. Click "Startup Parameters"
6. On the "specify a startup parameter" textbox type "-T272"
7. Click "Add"
8. Confirm the changes
I am still evaluating if I should opt for tf 272 or use a sequence generator with NO CACHE setting (http://msdn.microsoft.com/en-us/library/ff878091.aspx) as this documentation says Trace flags may not be supported post SQL 2012


Vishal Narayan Saxena

http://twitter.com/vishalishere http://www.ogleogle.com/vishal/
Posted by Fırat Esmer on 5/14/2013 at 11:05 PM
This is important.
Posted by Microsoft on 5/6/2013 at 3:53 PM
Hello all,

I am the dev owning the identity feature.
To boost the preformance for high end machines, we introduce preallocation for identity value in 2012.
And this feature can be disabled by using TF 272 (then you will get the behaviour from 2008R2).

The identity properties are stored separately in metadata.
If a value is used in identity and increment is called, then the new seed value will be set.
No operation, including Rollback, Failover, ..... can change the seed value except DBCC reseed.
Failover applies for the table object, but no the identity object.
So for failover, you can call checkpoint before manual failover, but you may see gap for unplanned cases.
If gap is a concern, then I suggest you to use TF 272.

For control manager shutdown, we have a fix for next verion (with another TF). This fix will take care of most control manager shutdown cases.

Thanks,
Bryan
Posted by LordFrankoo on 4/10/2013 at 6:50 AM
Add -T272 in startup parameters and IDENTITY jumping disabled
Posted by RobLobbe on 1/14/2013 at 3:22 PM
@Jan (Microsoft),

Granted, the SQL standard (2003) provided for sequences and the 'same-value-for-row' result.
However it also provides for IDENTITY with similar provisions eg.
IDENTITY (START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 10000
NO CYCLE),
This functionality wasn't provided. If it had been we could have simply re-provisioned the cache size for our identity columns (ie NO CACHE) and the problem would not have occurred - similarly if the 'random' cache size (CACHE UNKNOWN) had not been implemented for IDENTITY columns.

At this point I'll stick with the -t272 as it's the only way to keep multiple servers in-sync. (mirrors, clusters, replication) - The CACHE being a 'memory' object that is not shared between dependent servers. (which is were the problem lies - not so much the 'skipping' of values, although that's inconvenient)
Posted by xor88 on 1/13/2013 at 6:03 AM
It has been noted in this thread that a Service Control Manager STOP should CHECKPOINT all databases but doesn't. This is an unrelated bug but it is the trigger for the bug of this ticket. It causes service and server restart times to explode.

I opened a case for that here: https://connect.microsoft.com/SQLServer/feedback/details/776427/sql-server-service-control-manager-shutdown-does-not-checkpoint

Please vote.
Posted by miki72 on 1/11/2013 at 12:46 PM
I use in present SQl Server 2008 R2 after uninstal SQL 2012 and work one week to make chages in databases (i have over 100 databases attached on SQL Express). I tested on other pc SQL 2012. Using -T272 flag added to parameters on start and restart SQL Server no problem. I also tested with sequence and no cache for each table. Sequence must be created for each identity column in all tables from database and started with the last Identity value + 1 in table column. Some changes must be write to software code and replace SCOPE_IDENTITY().
Also is posible to use SQL 2008 on other pc, you must recreate database, tables, stored procedure and write query to move data from server to server. After that instal SQL 2008 instead of SQL 2012 and attach data again). Hard work.
Using -T272 is the best option until update database and your software.
Posted by jscriptr on 1/10/2013 at 11:05 AM
Any patches on this issue? I have the same problem and I believe it is because of a windows server restart after an update. Had to turn off the automatic updates on the server not to see this happening again!
Posted by SAinCA on 1/8/2013 at 12:26 PM
Restarted cluster node after patching. NOT any part of an Availability Group - simple active-passive fail-over cluster. Enterprise Edition. Added 1000 to our User ID table. Records are added occasionally and only SINGLY so there's no apparent reason why a "batch" greater than ONE would be handled/allocated/whatever.

Clearly a BUG!

Which CU will this go into, please, Microsoft. I'm already using CU5 and it isn't in there AFAIK.
Posted by BlackSeaCoder on 1/1/2013 at 5:22 AM
What is the solution now. 1000 is going to still dodging.
Posted by Peter3412 on 12/21/2012 at 1:51 PM
We went thru this same headache with sybase a decade ago. They had the ability to define acceptable gap rates on a per table basis using 'with identity_gap = x' syntax.

Occurances of normal gaps caused by aborted transactions or releases of magic smoke are not the same thing as huge gaps on order of thousands in response to a controlled shutdown. There are operational headaches these behaviors create separate from required tolerance of gaps occuring normally.

I recommend fixing the specific problem with gaps occuring on a controlled shutdown and provide an option like sybase to allow the acceptable batch size from seq pool to be explicitly declared. At the very least would much appreciate to know what MS plans are with regards to this problem so we can plan accordingly.
Posted by Webio on 12/18/2012 at 10:26 AM
Is there any documentation related to 272 flag? I'm interested how it will affect SQL Server and if it will be available in future versions of SQL Server. On the other hand isn't there any chance to have some kind of switch for Identity to make it working like it was in previous SQL Server versions (IMHO if DB is set to some older SQL Server compatibility then identity should also work like it was on selected version)?
Posted by miki72 on 12/14/2012 at 12:48 PM
Hi Vishal

Thank you for your answer even after few month, but now im using again SQL 2008R2 wich work fine. SQL 2012 remains for me just for test.

Best Regards,
Mike
Posted by Microsoft on 12/11/2012 at 4:30 PM
Hi RobLobbe,

The behavior you are seeing in your example is as defined in the SQL standard. For each generated row, the NEXT VALUE FOR function returns the same value for a given sequence no matter how often it is invoked.

Thanks,
Jan
Posted by Microsoft on 12/11/2012 at 12:51 PM
The TF is applicable for Indentity only...you can set the Trace Flag as a SQL Server startup option
http://msdn.microsoft.com/en-us/library/ms345416.aspx
http://technet.microsoft.com/en-us/library/ms190737(v=sql.110).aspx
Posted by Webio on 12/11/2012 at 2:42 AM
Hello,

is there a way to make "Use trace flag 272" be set permanently?

Regards
Posted by RobLobbe on 12/10/2012 at 4:03 PM
Also at the risk of going off topic... 'next value of' is transaction locked.

CREATE SEQUENCE s1 AS INT START WITH 1 NO CACHE;
go
CREATE TABLE t1 (
    i1 INT identity(1,1) PRIMARY KEY,
    c1 int not null default next value for s1,
    c2 int not null default next value for s1,
    d INT NOT NULL)
go
insert into t1(d) values (1)
insert into t1(d) values (2)
select * from t1

c1 & c2 have identical values despite requesting the next value.
Just a little example where it's not truly implemented as a sequence (easier to detect this way)
Posted by RobLobbe on 12/10/2012 at 3:16 PM
Vishal,

Does the trace flag 272 only effect Identity columns, or is this for all other sequences as well?
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
Posted by Webio on 11/28/2012 at 3:00 AM
I'm seriously dissapointed that no one from MS have responded here after this:

"Thanks for your feedback.

We're trying to quickly assess the impact of this issue.

Could people please reply indicating if they're hitting it failing over Availability Groups or doing something else (e.g. restarting server)?"

message.
Posted by RobLobbe on 11/25/2012 at 1:40 PM
The Identity column has always been sequentially incremented, and gaps only occurring via deletion (or increment value)

I understand the new sequence feature (it’s been in Oracle forever...)
The issue – I believe – comes down to how identity has be adapted to use the sequence technology.

This is intended behaviour when a sequence hasn’t used all its cached values, after a restart the first value of the next ‘block’ is used.
If the sequence cache is turned off, numbers are allocated in sequence (just as an identity column worked in previous editions)
The problem lies in that identity columns haven’t been given a fixed buffer size. The doco says that the ‘engine’ will determine what the buffer size is that it will use, and that this can change between buffer allocations – as no one outside MS knows the rules all gaps appear to be excessively large (for small sets in particular)

It’s well established that the identity is a pseudo row_id in development circles (from the application side) this new behaviour requires a change of thinking which a lot of people are finding difficult.

While Identity is a sequence, it has a specific, and limited, set of conditions to that provided the sequence functionality.
What’s more it’s allocation requirement vary vastly over the volatility of individual tables.

I personally would like to see a [NO] [CACHE [n]] extension to the identity syntax - to align it with sequence syntax.
This way identities could be redefined to allow pre2012 behaviour with NO CACHE (at a loss of performance over the ‘new’ way)

Identity cache/no cache could be a ‘default’ configuration option (sp_configure) to reassert previous identity functionality
-the above could change things for specific circumstances

Next value for <sequence> is also cumbersome – why did we have to use cursor syntax? (SQL/ANSI/ISO- Standards?)
Next(<sequence>) - it’s just a matter of parsing the syntax.

Identity columns should also appear in the sys.sequences view. (after all that’s what they are)

And I also noted that using ‘next value from’ as a default value for a column doesn’t work. The doco gives it as an example – but I haven’t been able to make it work, null = null, or null = syntax error, or put in a value – which defeats the purpose of the default
Posted by Webio on 11/21/2012 at 1:06 AM
There is new update SQL Server 2012 SP1 CU1:

http://support.microsoft.com/kb/2765331

but this time I also don't see anything regarding this issue.
Posted by djwiebelhaus on 11/19/2012 at 1:48 PM
This is happening without AlwaysOn. It is a drastic change in behavior that is causing possible critical failure in parts of our system.
Posted by Webio on 11/16/2012 at 12:38 PM
In my opinion it breaks previous functionality and there should be possible to enable/disable this preallocation of identity. According to this forum topic:

"I found a few related bugs in our internal bug database and learned that this is expected
behavior. There was a change to the identity code path that resulted in a
serious performance regression. In order to address the problem,
identities are now pre-allocated. The size of the pre-allocation is based
on the size of the data type of the column the identity property is defined on.
For a SQL Server integer column, the server pre-allocates identities in ranges
of 1000 values. For the bigint data type the server pre-allocates in ranges of
10000 values. Based on empirical testing using the database you uploaded,
it appears that a new range will be allocated for each table that had at least
one INSERT since the last time SQL Server was restarted. For tables that
utilize the identity property that didn’t allocate any new identity values
since the last time SQL Server was restarted, a new range is not pre-allocated
the next time the server starts."

so IMHO for users which are accepting mentioned performance regression there should be a switch in SQL Server configuration which would allow to keep things like they where in previous versions.
Posted by miki72 on 11/16/2012 at 11:50 AM
Look with carefull at this:
http://www.sqlserver-training.com/sequence-breaks-gap-in-numbers-after-restart-sql-server-gap-between-numbers-after-restarting-server/-
It's not a bug. I found it on Google and contains all we need know.
Posted by Dave Holsinger on 11/16/2012 at 8:27 AM
We just purchased and installed 2012, and we're seeing big gaps in identity columns after restarts, as well.

Thank you to those who have posted workarounds. Given the critical nature of this bug, we'd rather not use this build in production. Can someone from Microsoft please comment on the status of this? We have deadlines to hit and we're in limbo here, as are many observers of this thread, I'm sure...

Thanks!
Posted by YismanOl on 11/14/2012 at 12:16 AM
its nine months since the bug was reported!!
micro$oft couldn't care less about its business division
too busy with fancy surfaces to be bothered with commenting on such insignificant issues
guess ill have to put off 2012 for now
Posted by miki72 on 11/13/2012 at 4:02 PM
Just I'm finished a hard work to downgree to 2008 R2. Im happy. Maybe is not a bug, maybe is just for to accommodate to sequential increment... I will use SQL 2012 when i will need for them. Its a good lesson in future to avoid use identity columns for numbers of documents. What will do MS remain a curiosity for me. I hope to solve quicly this problem, not in 2013 or latter. I think more users then are here are afected by this.
Posted by html_gensolo on 11/13/2012 at 2:29 PM
I can't believe SP1 didn't address this... big problem! How can I implement this in production!? So now where I had four projects slated for SQL 2012 I have none!!! Not production ready!!!
Posted by Webio on 11/12/2012 at 11:48 PM
IMHO if this is a feature and not a bug then someone from MS should said it six months ago and don't keep this bug report active (and second one too).
Posted by miki72 on 11/12/2012 at 12:25 PM
I have over 100 databases wich use numbers generated by identity columns for documents. I use a solution posted here, but now im working hard to downgree to sql 2008 R2. Never dont upgrade to new version until they are fully tested. What is new isnt allways the best. MS can post an answer like this: is or not a bug, or this is sql 2012...
Are few months from this was reported and no answer or hope from MS. Im disapoint...
Posted by Webio on 11/12/2012 at 4:54 AM
I totally don't understand why someone from MS didn't replied to this problem (except message "thanks for feedback" one month ago). I've searched MS Connect and found one more bug report:

http://connect.microsoft.com/SQLServer/feedback/details/743300/identity-column-jumps-by-seed-value

from ... wait for it ... may and june. No one from MS responded under mentioned report. I have customers which their business invoice numbering is based on identity and our tax laws don't allow to have gaps between numbering so this is a huge problem for me. Once again I would like to ask for any feedback from MS.
Posted by miki72 on 11/11/2012 at 2:39 AM
I upgraded to new version and tested. Not solved. I'm sorry. If not solved in few month i will come back to 2008 R2.
In present i used a solution from workarounds Posted by GordonBeeming on 8/22/2012 at 5:51 PM
and work well.
Posted by Webio on 11/11/2012 at 1:32 AM
I've checked it before but I'm still hoping that it is fixed in SP1 but not listed in bugs list. Someone from MS SQL dev team should really add comment under this bug report.
Posted by miki72 on 11/10/2012 at 1:53 PM
See http://support.microsoft.com/kb/2674319
List of the bugs that are fixed in SQL Server 2012 Service Pack 1
Not in list this bug.
I have the same problem.
Posted by Webio on 11/7/2012 at 11:45 PM
Hello,

http://www.microsoft.com/en-us/download/details.aspx?id=35575

SQL Server 2012 SP1 has been released. Can someone confirm that this issue not exists in this version?

Regards
Posted by stmarti1 on 11/2/2012 at 5:11 AM
"If the identity value is already over 1000, no reseed occurs."

After each restart I get the reseed also when the identity is over 1000. I get similar identity values, example:
8
9
10
1018
1019
1020
1023
1024
1025
1026
2020
3020
Posted by stmarti1 on 11/2/2012 at 5:04 AM
For me, identity always(!) getting identity gaps 1000 after every restart (net stop msssqlserver, net start mssqlserver). 2012 developer edition.
Posted by Webio on 10/29/2012 at 2:41 AM
Is there any chance for some comment from MS SQL Server dev team?
Posted by shillbot on 10/24/2012 at 8:11 AM
I see it after restart.
Posted by Thisted on 10/24/2012 at 6:25 AM


Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
    Feb 10 2012 19:39:15
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Posted by Thisted on 10/24/2012 at 6:23 AM
I found this behavior today while testing failing over always on availability group.

1
2
3    
--Failed Over--
1003
--Failed Over--
2003
2004


Simple table
CREATE TABLE [dbo].[DBA](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NOT NULL
) ON [PRIMARY]
Posted by stefang_1234 on 10/22/2012 at 1:41 AM
There are actually two problems here:

1) The behavior of identity insert has changed with SQL 2012 so it does not write a log entry each time an identity columns is incremented. This is most likely a performance optimization that is by design.
2) When restarting SQL 2012 through NET STOP or using the restart command in SSMS, no checkpoints are performed.

Problem 2) is visible in the following way:

If you restart the server using NET STOP, or SSMS, gaps occur in the identity columns.
If you performa a CHECKPOINT before restarting, no gaps occur.
If you performa shutdown using the SHUTDOWN SQL command, no gaps occur.

To me this seems like there is a bug in the implementation of the SERVICE_CONTROL_STOP command in SQL 2012. It is supposed to perform a checkpoint in all databases (according to http://msdn.microsoft.com/en-us/library/ms188767.aspx) - but that is not happening.

If 2) is fixed, 1) would only cause problems when a server is stopped abruptly by terminating the process, or a power failure.

Posted by SQLServerSteve on 10/16/2012 at 6:01 PM
I posted an identity bug detection/reseed code generator on the Workaround tab and at this blog post: http://multidimensionalmayhem.wordpress.com/2012/10/16/another-stop-gap-solution-for-the-sql-server-2012-identity-crisis/

In that post I discuss a few other things I noticed about the bug:
1) It can lead to significant data loss when the INSERT of a new row leads a .Net app to generate a different identity value than the SQL Server database, thereby causing UPDATE statements to the new row to fail.

2) In many cases, as the procedure I posted here shows, the bug leads ident_current and the last_value column of sys.identity_columns to incorrectly report that the faux identity values have already been used, when a check of the Max(identity column name) shows that it has not. Either those first two functions are reporting incorrect results, or the server is actually generating the values and then deleting them.

3) In other circumstances, ident_current and last_value may be about equal to Max(identity_column_value), but the bug may have still left a gap in the table that shows up in a discrepancy with Count(*). This occurs when the bug has struck in the past, but the user has since successfully added rows with the new faux identity seed (about + 1,000).

4) In my case, it has only affected tables that had recent Insert or Update activity.

5) It definitely occurs on server restarts. I haven't used AlwaysOn yet, so in my case, that is not related to the errors. I cannot say for certain, but I thought it could have occurred been server restarts as well at times when I had been using single-user .Net apps for hours or days without interruption; then again, I wasn't looking for the error at the time, so I cannot be sure.

I hope this helps pin down the error a little more closely....

-- Steve B.
Posted by DikAlex on 10/16/2012 at 6:23 AM
I have in one of the tables "id" field, which is identity and now it goes like this
78889
78890
78891
78892
78893
108814
108815
108816
108817
and every insert batch starts at last "id" value + 10000.
Posted by Webio on 10/15/2012 at 8:52 AM
As a hosting provider I'm confirming that I'm also experiencing this issue after restarting SQL Server which are being used by customers. Any ETA for a fix?
Posted by mg.net on 10/8/2012 at 11:32 AM
Pretty simple:

- Install SQL-Server
- Create a table with an identity Primary key, let's say of type int
- Insert a few records - numbering is ok
- Restart SQL-Server using control Manager
- Insert a couple of Addition records
- You will notice gaps in identity. Maybe not always, but in most cases

This has nothing to do with availability Groups or failover
Posted by Peter3412 on 10/7/2012 at 2:20 AM
I can reproduce with a simple database - no availability groups while restarting SQL Server service cleanly from windows service manager.

While generally useful the workarounds present a risk in that identity values from deleted records could be reused. We are recommending customers hold off on 2012 until this gets fixed.
Posted by Microsoft on 10/4/2012 at 4:59 PM
Thanks for your feedback.

We're trying to quickly assess the impact of this issue.

Could people please reply indicating if they're hitting it failing over Availability Groups or doing something else (e.g. restarting server)?
Posted by lunter on 9/9/2012 at 2:21 AM
Is this bug has been fixed in SQL SERVER 2012 SERVICE PACK 1 (CTP3) ?
Posted by mikegoodtampa on 8/20/2012 at 11:16 AM
As of RTM anyway, I think statement "If the identity value is already over 1000, no reseed occurs" is not quite correct.

With ANY size table, the identity jumps (as stated by others) to approximately the next multiple 1000 with each failover. I see this happening with tables with few rows, and with tables having more than 1000 rows as well.

IDENTITY(1,1) populated with 1300 rows, jumps to 2002 after failover, then 3002, etc...
Posted by Leo.G.Miller on 6/20/2012 at 4:34 PM
After each restart, for a given identity (seed, increment) pair the jump will be to (N*increment*1000) + seed. Where N is big enough to create the first result greater than the last identity value.
examples:
IDENTITY (1,1) jumps to 1001, 2001, 3001, etc…
IDENTITY (5,10) jumps 10005, 20005, etc…

It looks like adapting the IDENTITY functionality to accommodate the new SEQUENCE function has caused this problem.

Leo Miller
Senior DBA Consultant
SQL Services Limited
New Zealand

Posted by RohitGarg on 6/14/2012 at 10:12 AM
I'm also getting same error on cluster with out always on, standalone without always on.
Posted by Chuck Pedretti on 6/14/2012 at 6:20 AM
I'm seeing this occur even when always on is not used. Just an identity column in a table which seems to jump to the next 1000 after each restart: (restarted service after 7 and 1004)
1
2
3
4
5
6
7
1002
1003
1004
2002
Posted by AaronBertrand on 6/14/2012 at 4:53 AM
I can reproduce this and it definitely does not rely on AlwaysOn being configured at all, or a Failover occurring. And this also happens on multiple successive restarts, jumping by about 1000 every time.
Posted by BlackSeaCoder on 4/30/2012 at 9:04 AM
Hi. I think the first turkey this error happened to me. just started at the following topics.

http://forums.asp.net/t/1797416.aspx/1?SQL+2012+Identy+Column

I can not stop or restart the server for a few days. or the server can not start again. 1000 which is open all the databases, because the identity values ​​rise. Please correct this error as soon as possible. This is a significant error. 100,101,102 saddle while id tables, etc., 1002, 1003, etc., continues to form. sql id start again when he gives the 2003.2004.
Posted by Mehmet GÜZEL on 4/27/2012 at 12:03 AM
This case is repeated after every restart of SQL Server 2012 Database Engine Service.
Posted by AaronBertrand on 4/26/2012 at 6:56 AM
I think it's important to note that, according to Robert (I am at the dentist so haven't tested this), the symptom can occur even for databases that are not in an Availability Group, on servers that aren't enabled for AlwaysOn. Hopefully that amplifies the customer mpact and how end users view the criticality of the bug.
Posted by Robert L Davis on 4/26/2012 at 6:44 AM
Possibly related to the following internal bugs:

VSTS # 552380 : IDENTITY column: inconsistence:: after sql restart, identity column will start from 12, not from 1 above the max value of identity column causing backward compatibility issues.
VSTS # 444456 : IDENTITY column: inconsistence:: after sql restart, identity column will start from 12, not from 1 above the max value of identity column

Also filed for the RTM branch: VSTS # 903188

Sign in to post a workaround.
Posted by bimarsh on 5/9/2014 at 12:59 AM
If I am not getting it wrong, this is the simple workaround I would suggest - Create a TRIGGER on INSERT on the table you are having IDENTITY. Inside trigger definition, just do a reseeding of identity with the max value of the identity column in that table. Example below:

USE [TEST]

CREATE TABLE TEST(ID INT IDENTITY(1,1),VAL VARCHAR(10))

CREATE TRIGGER TGR_TEST_IDENTITY ON TEST
FOR INSERT
AS
DECLARE @RESEEDVAL INT
SELECT @RESEEDVAL = MAX(ID) FROM TEST
DBCC CHECKIDENT('TEST', RESEED, @RESEEDVAL)

INSERT INTO TEST(VAL)VALUES('a')

SELECT * FROM TEST

Thanks.
Posted by SQLServerSteve on 10/16/2012 at 5:49 PM
Here is a cursorless workaround that will provide more information about identity discrepancies in all of your user tables, plus auto-generate the DBCC CHECKIDENT code needed to reseed them all, if necessary. This procedure compares the last_value from sys.identity_columns, the row Count, the results of the IDENT_CURRENT function and the Max value actually in the identity column, for each user table in a given database. If the CountDifference, MaxIDDifference or LastIDValueDifference is around 1,000, it's probably due to the identity bug. You may need to edit it to suit your own needs, as I discuss in greater detail in my blog post at http://multidimensionalmayhem.wordpress.com/2012/10/16/another-stop-gap-solution-for-the-sql-server-2012-identity-crisis/ .

ALTER PROCEDURE [dbo].[ReturnIdentityInfoSP]
AS
BEGIN



DECLARE @SchemaName nvarchar(128),
@TableName nvarchar(128),
@ColumnName nvarchar(128)

DECLARE @MyTableVar table(
        ID bigint IDENTITY (1,1) PRIMARY KEY CLUSTERED,
     SchemaName nvarchar(128),
     TableName nvarchar(128),
     ColumnName nvarchar(128),
     object_id int,
     last_value sql_variant,
     Count bigint,
     LastUsedIDValue bigint,
     CountDifference bigint,
     LastIDDifference bigint,
     GeneratedDBCCCode nvarchar(max),
     MaxID bigint
     )



INSERT INTO @MyTableVar
(SchemaName, TableName, ColumnName, object_id, last_value, Count, LastUsedIDValue, CountDifference, LastIDDifference)
SELECT SCHEMA_NAME(schema_id) AS SchemaName, T1.name AS TableName, T2.name AS ColumnName, T2.object_id,
last_value, T3.Rows AS Count, ident_current(SCHEMA_NAME(schema_id) + '.' + '[' + T1.name + ']') AS LastUsedIDValue,
CAST(last_value AS bigint) - T3.Rows AS CountDifference, CAST(last_value AS bigint) - CAST(ident_current(SCHEMA_NAME(schema_id) + '.' + '[' + T1.name + ']') AS bigint) AS LastIDDifference
-- 'DBCC CHECKIDENT (''' + SCHEMA_NAME(schema_id) + '.' + T1.name + ''' , RESEED, ' + CAST(MaxID AS nvarchar(50)) + ')' AS GeneratedDBCCCode

FROM sys.tables AS T1
     INNER JOIN sys.identity_columns AS T2
     ON T1.object_id = T2.object_id
        INNER JOIN dbo.SysIndexes AS T3
        ON T3.ID    = T1.object_ID
WHERE TYPE_DESC = 'USER_TABLE'
AND is_identity = 1
AND T3.IndID < 2
AND OBJECTPROPERTY(T1.object_ID,'IsMSShipped') = 0

DECLARE @CurrentTableVarID bigint = 0,
@MaxTableVarID bigint = 0,
@CounterCheck bigint = 0,
@SQLString nvarchar(max),
@ParameterDefinition nvarchar(500),
@MaxID bigint,
@MaxIDOut bigint

SELECT @MaxTableVarID = Max(ID) FROM @MyTableVar GROUP BY ID ORDER BY ID ASC
SELECT @CurrentTableVarID =Max(ID) FROM @MyTableVar GROUP BY ID ORDER BY ID DESC

WHILE @CurrentTableVarID <= @MaxTableVarID
BEGIN

        SELECT @SchemaName = SchemaNAme, @TableName = TableName,
        @ColumnName = ColumnName
        FROM @MyTableVar
        WHERE ID = @CurrentTableVarID

        SET @ParameterDefinition = '@MaxIDOut bigint OUTPUT';
        SET @SQLString = 'SELECT @MaxIDOut = Max(' + @ColumnName + ') FROM [' + @SchemaName + '].[' + @TableName + '] GROUP BY ' + @ColumnName + ' ORDER BY ' + @ColumnName + ' ASC'
        
        EXEC sp_executesql @SQLString, @ParameterDefinition, @MaxIDOut = @MaxID OUTPUT

        UPDATE @MyTableVar
        SET MaxID = @MaxID
        WHERE ID = @CurrentTableVarID

        /*
        INSERT INTO @MaxIDTableVar
        (ParentID, MaxID)
        EXEC    [dbo].[ReturnColumnMaxSP]
                @SchemaName = @SchemaName,
                @TableName = @TableName,
                @ColumnName = @ColumnName,
                @ParentID = @CurrentTableVarID
        */

        SET        @CounterCheck = @CounterCheck + 1
    SET @CurrentTableVarID = @CurrentTableVarID + 1 -- increment the loop
END


SELECT SchemaName, TableName, ColumnName, object_id, last_value, Count, LastUsedIDValue, MaxID, CountDifference, LastIDDifference, CAST(last_value AS bigint) - MaxID AS MaxIDDifference,
'DBCC CHECKIDENT (''' + SchemaName + '.' + TableName + ''' , RESEED, ' + CAST(MaxID AS nvarchar(50)) + ')' AS GeneratedDBCCCode
FROM @MyTableVar
ORDER BY MaxIDDifference DESC
END
Posted by Uwe Ricken on 10/10/2012 at 2:53 AM
Hallo Leo,
hallo Gordon,

your both solutions will not work in a published database with MERGE REPLICATION!
Posted by Gordon Beeming on 8/22/2012 at 5:51 PM
A full instance workaround would be to use the code below. It uses the same principal as Leo.G.Miller mentioned just for automatically does every identity column in the SQL instance

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


USE master;
GO
CREATE PROCEDURE sp_FixSeeds2012
AS
BEGIN

    --foreach database
    DECLARE @DatabaseName varchar(255)
    
    DECLARE DatabasesCursor CURSOR READ_ONLY
    FOR
        SELECT name
        FROM sys.databases
        where name not in ('master','tempdb','model','msdb') and sys.databases.state_desc = 'online'

    OPEN DatabasesCursor

    FETCH NEXT FROM DatabasesCursor
    INTO @DatabaseName

    WHILE @@FETCH_STATUS = 0
    BEGIN
    
        EXEC ('USE '+@DatabaseName + '

        --foreach identity column
        DECLARE @tableName varchar(255)
        DECLARE @columnName varchar(255)
        DECLARE @schemaName varchar(255)
    
        DECLARE IdentityColumnCursor CURSOR READ_ONLY
        FOR
        
            select TABLE_NAME , COLUMN_NAME, TABLE_SCHEMA
            from INFORMATION_SCHEMA.COLUMNS
            where COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, ''IsIdentity'') = 1
        

        OPEN IdentityColumnCursor

        FETCH NEXT FROM IdentityColumnCursor
        INTO @tableName, @columnName, @schemaName

        WHILE @@FETCH_STATUS = 0
        BEGIN
        
            print ''['+@DatabaseName+'].[''+@tableName+''].[''+@schemaName+''].[''+@columnName+'']''
            EXEC (''declare @MAX int = 0
                    select @MAX = max(''+@columnName+'') from ['+@DatabaseName+'].[''+@schemaName+''].[''+@tableName+'']
                    if (@MAX IS NULL)
                    BEGIN
                        SET @MAX = 0
                    END
                    DBCC CHECKIDENT(['+@DatabaseName+'.''+@schemaName+''.''+@tableName+''],RESEED,@MAX)'')

            FETCH NEXT FROM IdentityColumnCursor
            INTO @tableName, @columnName, @schemaName

        END

        CLOSE IdentityColumnCursor
        DEALLOCATE IdentityColumnCursor')



        FETCH NEXT FROM DatabasesCursor
        INTO @DatabaseName

    END

    CLOSE DatabasesCursor
    DEALLOCATE DatabasesCursor
END
GO

EXEC sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE
GO
EXEC sp_configure 'scan for startup procs', 1 ;
GO
RECONFIGURE
GO



EXEC sp_procoption @ProcName = 'sp_FixSeeds2012'
    , @OptionName = 'startup'
    , @OptionValue = 'true'
GO
Posted by Leo.G.Miller on 6/20/2012 at 4:29 PM
Enable "scan for startup procs configuration.
In Master add one or more stored procs as required to reseed each table based on the code here.

create proc FixIdent_DBName_Table
as
begin
declare @MAX int
select @MAX = max(Identity_Col) from DBName.dbo.Table
DBCC CHECKIDENT([DBName.dbo.Table],RESEED,@MAX)
end

sp_procoption @ProcName = 'FixIdent_DBName_Table'
    , @OptionName = 'startup'
    , @OptionValue = 'true'

After the restart the seed will be reset correctly.

Leo Miller
Senior DBA Consultent
SQL Services Limited
New Zealand