Next value for SEQUENCE can fail to return proper next value - by jchampagne

Status : 

  Not Reproducible<br /><br />
		The product team could not reproduce this item with the description and steps provided.<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 790161 Comments
Status Closed Workarounds
Type Bug Repros 2
Opened 6/14/2013 1:21:07 AM
Access Restriction Public


This is not an easily reproduced condition - it happens every now and then in a development environment I own. Basically under rather heavy load sometimes I will get an error like:  "Violation of PRIMARY KEY constraint 'PK__StoredTraceSQL__6A43A4A7'. Cannot insert duplicate key in object 'dbo.StoredTraceSQL'. The duplicate key value is (-9223372036853896059).
The statement has been terminated."  In this case, the PK is a bigint with a DEFAULT that's NEXT VALUE FOR a SEQUENCE. If I manually reseed / restart the sequence with the table's MAX value + 1, things start to work again - until the problem randomly happens again in the future.

I know no-one likes the "I can't reproduce it easily", but this is actually happening for me, and I'm hoping perhaps someone else has seen it.
Sign in to post a comment.
Posted by EugenePac on 6/26/2014 at 7:52 PM
We've experienced a similar issue when doing a database backup.

After restoring the database the current value of the sequence may be up to 10 less than the Primary Key on a table.

(Caching is set to DEFAULT).
Posted by Steve Powell on 1/24/2014 at 1:01 AM
Interestingly it seems this is only failing on 2 environments that have the build number 11.0.3000.0 which was the first release of SQL2012 SP1, a subsequent release 11.0.3128.0 which fixed some installer issues seems to have also corrected this problem.

The other environments seem to have

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)

and do not exhibit the same behaviour. So it would seem this was fixed between these two builds and it is a legacy issue of the mixed platforms we have.
Posted by Steve Powell on 1/22/2014 at 7:05 AM
See my note here for details of an instance of this that should be easy to reproduce

Posted by ct5125 on 11/5/2013 at 4:13 AM
Yes, I have seen something similar a couple of times, with the PK set in the same way. I can't yet reproduce it though, and the very next insert into the table will also assign the correct value. I've only seen it under heavy load.
Posted by Microsoft on 8/19/2013 at 10:24 AM
Thank you for submitting this issue. Since we have received no response to our requests for a repro, we are closing this case. Please open a CSS support case or reactivate this case, if you encounter this issue again.

Thanks again for your continued support in our products.
Posted by Microsoft on 7/25/2013 at 12:15 PM
Thank you for submitting this feedback.

Could you please provide us with the schema, data and a detailed description of the steps required to reproduce this issue.

Thanks again for reporting the product issue and continued support in improving our product.