Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Duplicate key issue ... again ... and again ... Expand / Collapse
Author
Message
Posted Friday, March 28, 2014 8:01 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 11:15 AM
Points: 384, Visits: 1,270
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.
Post #1555922
Posted Friday, March 28, 2014 8:03 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:33 PM
Points: 4,358, Visits: 6,193
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!!



Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1555925
Posted Friday, March 28, 2014 8:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:56 PM
Points: 6,365, Visits: 13,695
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"
Post #1555955
Posted Friday, March 28, 2014 9:26 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 11:15 AM
Points: 384, Visits: 1,270
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.
Post #1556004
Posted Monday, June 9, 2014 12:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 1:36 PM
Points: 2, Visits: 22
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?
Post #1578942
Posted Monday, June 9, 2014 1:12 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 11:15 AM
Points: 384, Visits: 1,270
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.
Post #1578948
Posted Tuesday, June 10, 2014 8:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 10:44 AM
Points: 48, Visits: 157
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.

Post #1579225
Posted Tuesday, June 10, 2014 10:11 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 11:15 AM
Points: 384, Visits: 1,270
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.
Post #1579304
Posted Tuesday, June 10, 2014 10:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:34 PM
Points: 13,481, Visits: 12,342
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)
Post #1579305
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse