SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Duplicate key issue ... again ... and again ...


Duplicate key issue ... again ... and again ...

Author
Message
sql-lover
sql-lover
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1625 Visits: 1930
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.
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12833 Visits: 8565
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
Perry Whittle
Perry Whittle
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20444 Visits: 17244
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 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


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" ;-)
sql-lover
sql-lover
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1625 Visits: 1930
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 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


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.
thomaswoof
thomaswoof
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 27
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?
sql-lover
sql-lover
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1625 Visits: 1930
thomaswoof (6/9/2014)
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?


Unfortunately and for my particular case, Yes.

I've not found any workaround or fix on this. I keep checking for SQL2012 hotfixes every month. And like I said before, this is not a code issue and everything was perfectly fine on SQL 2008 and SQL 2005.
SQL is delicious
SQL is delicious
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 174
sql-lover (6/9/2014)
thomaswoof (6/9/2014)
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?


Unfortunately and for my particular case, Yes.

I've not found any workaround or fix on this. I keep checking for SQL2012 hotfixes every month. And like I said before, this is not a code issue and everything was perfectly fine on SQL 2008 and SQL 2005.


If you don't post your code and tables, all anyone here can do is speculate.
sql-lover
sql-lover
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1625 Visits: 1930
SQL is delicious (6/10/2014)
sql-lover (6/9/2014)
thomaswoof (6/9/2014)
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?


Unfortunately and for my particular case, Yes.

I've not found any workaround or fix on this. I keep checking for SQL2012 hotfixes every month. And like I said before, this is not a code issue and everything was perfectly fine on SQL 2008 and SQL 2005.


If you don't post your code and tables, all anyone here can do is speculate.



I am not speculating. On my case, is a random problem on SQL 2012.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26620 Visits: 17557
sql-lover (6/10/2014)
SQL is delicious (6/10/2014)
sql-lover (6/9/2014)
thomaswoof (6/9/2014)
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?


Unfortunately and for my particular case, Yes.

I've not found any workaround or fix on this. I keep checking for SQL2012 hotfixes every month. And like I said before, this is not a code issue and everything was perfectly fine on SQL 2008 and SQL 2005.


If you don't post your code and tables, all anyone here can do is speculate.



I am not speculating. On my case, is a random problem on SQL 2012.


Nobody said you are speculating. The problem is we don't know what your tables and your code is like. Therefore WE are speculating.

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search