Failover or Restart Results in Reseed of Identity - by Grant Fritchey

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 739013 Comments
Status Closed Workarounds
Type Bug Repros 90
Opened 4/26/2012 6:38:41 AM
Access Restriction Public


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.
Sign in to post a comment.
Posted by Sai Nuthalapati on 3/29/2017 at 8:11 AM
I still the issue with SQL 2014 SP2 CU3. Looks like this is not yet fixed. I will try with option -t272
Posted by AlaskanRick on 12/22/2015 at 12:13 PM
We're a medium shop (~100 sql servers, 7-800 databases) running primarily on 2008R2. I've spent the last 2 years evangelizing sql 2012, and just about have everyone talked into upgrading. That's not going to happen now because of this BUG. Most of our tables have identity fields. Upgrading will make them all vulnerable to this PROBLEM. I've discovered that identity fields can be replaced with primary key integer fields populated using sequences, but rewriting tens of thousands of insert statements to use this functionality is not going to happen. This will require massive rewrites of all our code base to accommodate this very STUPID decision by microsoft. This, along with the new core-based licensing model, is leading me to recommend we move to Postgres SQL on Linux servers. If we're rewriting our code base anyway, we might as well move to a far more stable server platform in the process.
Posted by alcaraz73 on 3/26/2015 at 2:20 PM
Just happen on an Azure Database.
Posted by haggis999 on 2/23/2015 at 12:43 PM
I have just had my first sudden and unexpected jump (by 1,000) in the primary key of one of my tables. Before this, I had never heard of this obscure bug. It is almost unbelievable that Microsoft should implement such a change in this manner.

My SQL server databases are under the control of my ISP, which almost certainly means that I do not have the authority to change startup parameters. Does this mean that I have no way to make SQL Server's identity keys increment in their previous sane and simple manner?
Posted by BobSwi on 2/5/2015 at 10:11 AM
Tested a VM with SQL 2014 (12.0.2254) and the same behavior is exhibited. Startup parameters -t272 remedies it.
Posted by LordFrankoo on 1/23/2015 at 9:35 AM
This is not fixed , you need to add -T272 in sql server startup parameters for SQL 2012
Posted by anigeorge on 8/27/2014 at 11:41 AM
This issue is not resolved and don't know why its closed.

We recently upgraded from SQL server 2008 R2 to 2012. All our table are having bigint primary auto increment keys. We recently noticed that in one of our tables, the ID went to 10007 from 9. This is crazy. And the work arounds listed are more crazy.

When this will be resolved?
Posted by Scott Vanderlip on 8/21/2014 at 10:36 AM
HELP! - I see this issue is closed, but I'm still not sure if it has been fixed! Can anybody from MS please confirm that identity columns work as they should always have worked which is pretty basic and standard which is

(1) You define the database column with starting identity and seed

When you create a new row, that row will find the highest current value for that row and add the seed to it and presto, you are done! We have developed and sell a help desk ticketing system that depends upon these sequential case numbers. Please let developers and DB managers know if this has been fixed or when it will be fixed.

Those work arounds listed are just too funny.. what use to work without any code based on a very exact, very basic and understandable column identity specification now requires a complicated 30 line SQL fix? I have many clients upgrading our systems to SQL 2012 and this feature may break their systems.
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?


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 KAndrzej 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) ,
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.

Then it will work properly.

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
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 ( as this documentation says Trace flags may not be supported post SQL 2012

Vishal Narayan Saxena
Posted by Fırat Esmer on 5/14/2013 at 11:05 PM
This is important.
Posted by Bryan [MSFT] 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.

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.
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:

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,
Posted by Jan [MSFT] 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.

Posted by Microsoft on 12/11/2012 at 12:51 PM
The TF is applicable for Indentity can set the Trace Flag as a SQL Server startup option
Posted by Webio on 12/11/2012 at 2:42 AM

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

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.

    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,
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

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
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(
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:

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
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)?"

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:

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:
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...

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:

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
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

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

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:
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.

--Failed Over--
--Failed Over--

Simple table
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NOT NULL
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 - 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:

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
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 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 Luis [MSFT] 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.
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)
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.

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