Click here to monitor SSC
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
SSChasing Mays
SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)

Group: General Forum Members
Points: 651 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
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5990 Visits: 8314
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 at GMail
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8789 Visits: 16560
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
SSChasing Mays
SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)

Group: General Forum Members
Points: 651 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 (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 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
SSChasing Mays
SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)

Group: General Forum Members
Points: 651 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 Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 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
SSChasing Mays
SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)

Group: General Forum Members
Points: 651 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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16626 Visits: 17024
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 Moden's 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