March 27, 2014 at 9:02 am
Is someone else experiencing this problem, SQL2012, of course:
Violation of PRIMARY KEY constraint 'PK_MyTable'. Cannot insert duplicate key in object 'dbo.MyTable'. The duplicate key value is (1842649).
The statement has been terminated.
This never, ever, happened to me before on my MS-SQL 2008 or MS-SQL 2005 systems. Once I moved to MS-SQL2012, I started having these errors, which are a pain in the butt, especially for my Developers.
For some weird reason, MS-SQL losses track of the most recent ID value and try to insert and old one. And no ... the store procedure is the only one doing the inserts, and like I said, never had that issue before.
My new variables are:
-MS-SQL2012 (had SQL2005/2008 before)
-SAN
-VMware (had physical boxes before)
I fixed it via DBCC command, but I should not being doing this manually, MS-SQL2012 should not insert any existing value on my tables.
Is any one aware of any bug and a fix for this? I am aware of the existing bug when SQL is restarted, but this is not the case here.
March 27, 2014 at 9:24 am
sql-lover (3/27/2014)
Is someone else experiencing this problem, SQL2012, of course:
Violation of PRIMARY KEY constraint 'PK_MyTable'. Cannot insert duplicate key in object 'dbo.MyTable'. The duplicate key value is (1842649).
The statement has been terminated.
This never, ever, happened to me before on my MS-SQL 2008 or MS-SQL 2005 systems. Once I moved to MS-SQL2012, I started having these errors, which are a pain in the butt, especially for my Developers.
For some weird reason, MS-SQL losses track of the most recent ID value and try to insert and old one. And no ... the store procedure is the only one doing the inserts, and like I said, never had that issue before.
My new variables are:
-MS-SQL2012 (had SQL2005/2008 before)
-SAN
-VMware (had physical boxes before)
I fixed it via DBCC command, but I should not being doing this manually, MS-SQL2012 should not insert any existing value on my tables.
Is any one aware of any bug and a fix for this? I am aware of the existing bug when SQL is restarted, but this is not the case here.
I have not experienced anything like this or heard of it.
You didn't provide a lot of detail here. Are these inserts using identity columns or some sort of other way to determine what value to insert? I sort of get the impression this is identity.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 27, 2014 at 9:27 am
Sean Lange (3/27/2014)
sql-lover (3/27/2014)
Is someone else experiencing this problem, SQL2012, of course:
Violation of PRIMARY KEY constraint 'PK_MyTable'. Cannot insert duplicate key in object 'dbo.MyTable'. The duplicate key value is (1842649).
The statement has been terminated.
This never, ever, happened to me before on my MS-SQL 2008 or MS-SQL 2005 systems. Once I moved to MS-SQL2012, I started having these errors, which are a pain in the butt, especially for my Developers.
For some weird reason, MS-SQL losses track of the most recent ID value and try to insert and old one. And no ... the store procedure is the only one doing the inserts, and like I said, never had that issue before.
My new variables are:
-MS-SQL2012 (had SQL2005/2008 before)
-SAN
-VMware (had physical boxes before)
I fixed it via DBCC command, but I should not being doing this manually, MS-SQL2012 should not insert any existing value on my tables.
Is any one aware of any bug and a fix for this? I am aware of the existing bug when SQL is restarted, but this is not the case here.
I have not experienced anything like this or heard of it.
You didn't provide a lot of detail here. Are these inserts using identity columns or some sort of other way to determine what value to insert? I sort of get the impression this is identity.
Yes, you are right. There is a column that uses Identity. So sometimes, the nightly batches fail because the Identity value that the sproc is about to insert is lower than what it is.
I have no technical data to prove is the SAN, or VMware, but I can't say is MS-SQL2012 either, as I never had that issue before. And this happens sporadically. Sometimes one or two times in a week. Sometimes one per month.
I think the ID values are generating on SQL2012 in a different way, but I am not sure. What I am know for sure, is that this is not a code issue and this is not normal.
March 27, 2014 at 2:41 pm
This is a concurrency issue, therefore I am 99.984% certain it is a CODE issue. Something somewhere is not properly handling locking and concurrency. As for why this is now starting on 2012 but did not on older SQL Server the most likely cause is that the optimizer is coming up with a different plan that is allowing it to happen and your code isn't dealing with it properly. the 0.016% chance could be a bug.
And like someone else said, you didn't tell us much at all about details of code, structures, indexing, the other processes other than the sproc, etc., etc.
Did you update statistics with a full scan after upgrading to 2012? Also, did you update ANY piece of hardware or the VM during the upgrade?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 27, 2014 at 3:14 pm
TheSQLGuru (3/27/2014)
This is a concurrency issue, therefore I am 99.984% certain it is a CODE issue. Something somewhere is not properly handling locking and concurrency. As for why this is now starting on 2012 but did not on older SQL Server the most likely cause is that the optimizer is coming up with a different plan that is allowing it to happen and your code isn't dealing with it properly. the 0.016% chance could be a bug.And like someone else said, you didn't tell us much at all about details of code, structures, indexing, the other processes other than the sproc, etc., etc.
Did you update statistics with a full scan after upgrading to 2012? Also, did you update ANY piece of hardware or the VM during the upgrade?
?
Why is a concurrency issue? Locking mechanism did not change on 2012. Like I said, there is only one sproc that is always inserting there, so no way it can be a concurrency issue. And if that was the case, inserts lock the required pages or table, and block anything else ... insert ... and then sql increases the value. So next insert should use next ID, already increased.
I am almost sure this is a bug, but (or a VMware/SAN issue) but I can't reproduce.
March 27, 2014 at 5:24 pm
sql-lover (3/27/2014)
TheSQLGuru (3/27/2014)
This is a concurrency issue, therefore I am 99.984% certain it is a CODE issue. Something somewhere is not properly handling locking and concurrency. As for why this is now starting on 2012 but did not on older SQL Server the most likely cause is that the optimizer is coming up with a different plan that is allowing it to happen and your code isn't dealing with it properly. the 0.016% chance could be a bug.And like someone else said, you didn't tell us much at all about details of code, structures, indexing, the other processes other than the sproc, etc., etc.
Did you update statistics with a full scan after upgrading to 2012? Also, did you update ANY piece of hardware or the VM during the upgrade?
?
Why is a concurrency issue? Locking mechanism did not change on 2012. Like I said, there is only one sproc that is always inserting there, so no way it can be a concurrency issue. And if that was the case, inserts lock the required pages or table, and block anything else ... insert ... and then sql increases the value. So next insert should use next ID, already increased.
I am almost sure this is a bug, but (or a VMware/SAN issue) but I can't reproduce.
I was going to ask the same question. How would a concurrency issue cause a dupe with an IDENTITY column?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 28, 2014 at 6:16 am
Another thought, are there assumptions built into the code that the next value will always be one greater, because starting in SQL Server 2012, there can be large gaps in IDENTITY columns. There's a bug report and a series of work arounds if those gaps cause problems for you here on Connect.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 28, 2014 at 7:02 am
Can you post the table definition and the procedure?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 28, 2014 at 7:21 am
The default behaviour for an identity column now is to increase the seed after a restart or failover, etc. By doing this
sql-lover (3/27/2014)
I fixed it via DBCC command
you are likely compounding the issue you are seeing. You should not have touched it. By setting the seed back it will then increment forward and hit a value that's already previously used.
There is a trace flag -T272 to make the database engine behave like SQL Server 2008. Its all detailed here
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 28, 2014 at 7:55 am
Perry Whittle (3/28/2014)
The default behaviour for an identity column now is to increase the seed after a restart or failover, etc. By doing thissql-lover (3/27/2014)
I fixed it via DBCC commandyou are likely compounding the issue you are seeing. You should not have touched it. By setting the seed back it will then increment forward and hit a value that's already previously used.
There is a trace flag -T272 to make the database engine behave like SQL Server 2008. Its all detailed here
You misread what I said. Of you are confused.
If I don't reset the ID, my jobs keep failing, they keep using a lower ID. What I did was the correct and right thing. If the job is trying to insert a record with ID equals to 8, but last value in table and memory is 10, I need to reset via DBCC because it keeps failing, creating a duplicate ID error. That's an ID and column that is auto generated by SQL server, an auto increment. For some weird reason, the value goes down (or got stuck in memory? I don't know)
My thread is to know if someone else is aware of this bug. It could be VMware, it could be the SAN. NO! It is NOT a code issue.
I may try later and post table's definition though.
March 28, 2014 at 8:01 am
GilaMonster (3/28/2014)
Can you post the table definition and the procedure?
Gail,
That's actually a good suggestion. I'll do it later during the day.
March 28, 2014 at 8:03 am
I failed to catch the follow-up post about there being an identity column involved. Sorry!
Note to all - don't answer forum posts while on a cruise!! :blink:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 28, 2014 at 8:36 am
sql-lover (3/28/2014)
Perry Whittle (3/28/2014)
The default behaviour for an identity column now is to increase the seed after a restart or failover, etc. By doing thissql-lover (3/27/2014)
I fixed it via DBCC commandyou are likely compounding the issue you are seeing. You should not have touched it. By setting the seed back it will then increment forward and hit a value that's already previously used.
There is a trace flag -T272 to make the database engine behave like SQL Server 2008. Its all detailed here
You misread what I said. Of you are confused.
If I don't reset the ID, my jobs keep failing, they keep using a lower ID. What I did was the correct and right thing. If the job is trying to insert a record with ID equals to 8, but last value in table and memory is 10, I need to reset via DBCC because it keeps failing, creating a duplicate ID error. That's an ID and column that is auto generated by SQL server, an auto increment. For some weird reason, the value goes down (or got stuck in memory? I don't know)
My thread is to know if someone else is aware of this bug. It could be VMware, it could be the SAN. NO! It is NOT a code issue.
I may try later and post table's definition though.
I'm not confused, but your post is unclear
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 28, 2014 at 9:26 am
Perry Whittle (3/28/2014)
sql-lover (3/28/2014)
Perry Whittle (3/28/2014)
The default behaviour for an identity column now is to increase the seed after a restart or failover, etc. By doing thissql-lover (3/27/2014)
I fixed it via DBCC commandyou are likely compounding the issue you are seeing. You should not have touched it. By setting the seed back it will then increment forward and hit a value that's already previously used.
There is a trace flag -T272 to make the database engine behave like SQL Server 2008. Its all detailed here
You misread what I said. Of you are confused.
If I don't reset the ID, my jobs keep failing, they keep using a lower ID. What I did was the correct and right thing. If the job is trying to insert a record with ID equals to 8, but last value in table and memory is 10, I need to reset via DBCC because it keeps failing, creating a duplicate ID error. That's an ID and column that is auto generated by SQL server, an auto increment. For some weird reason, the value goes down (or got stuck in memory? I don't know)
My thread is to know if someone else is aware of this bug. It could be VMware, it could be the SAN. NO! It is NOT a code issue.
I may try later and post table's definition though.
I'm not confused, but your post is unclear
Let's leave it like that 🙂
I said the ID to be inserted was low, when it suppose to be higher, so I don't see how that's unclear. Only way to fix is via DBCC. What I did was correct.
June 9, 2014 at 12:50 pm
I'm having the exact same problem. The table had at some point been reseeded while importing data from the old version of the software. So apparently somehow the reseed value was too low.
Our max identifier value was 168, but any kind of INSERT (directly from SSMS, whatever), gave the duplicate key error message, starting at 158. Each INSERT run would give the same error message with the key value one higher. After the error with key value 168, the INSERTS were successful.
So is a DBCC CHECKIDENT the only way of solving this?
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply