November 25, 2009 at 10:40 am
My table of 35 million plus rows gave me a INSERT failure of PK last night. The PK is on the IDENTITY column which is just a 1,1 incremental.
When I ran DBCC CHECKIDENT(tablename) it returned a current identity value 35,678,123 and current column value 35,683,456
so the current identity value is less than the current column value. I ran DBCC CHECKIDENT(tblname,RESEED) and all is well.
My question is, how did this happen? Our software only inserts values, no trucations, no forced reseeds or even record deletions for that matter.
November 25, 2009 at 11:15 am
Dan Guzman - Not the MVP (11/25/2009)
My table of 35 million plus rows gave me a INSERT failure of PK last night. The PK is on the IDENTITY column which is just a 1,1 incremental.When I ran DBCC CHECKIDENT(tablename) it returned a current identity value 35,678,123 and current column value 35,683,456
so the current identity value is less than the current column value. I ran DBCC CHECKIDENT(tblname,RESEED) and all is well.
My question is, how did this happen? Our software only inserts values, no trucations, no forced reseeds or even record deletions for that matter.
That may be related to rollback transactions because that is one of main seed increment issues in the IDENTITY property which Microsoft implemented from ANSI SQL.
Kind regards,
Gift Peddie
November 25, 2009 at 11:18 am
wouldn't a rollback also have removed the records inserted? The current coulmn value should have gone down as well, I would think.
November 25, 2009 at 11:24 am
Dan Guzman - Not the MVP (11/25/2009)
wouldn't a rollback also have removed the records inserted? The current coulmn value should have gone down as well, I would think.
That is the issue it removes the values but not the IDENTITY associated with the value that is the reason SCOPE_IDENTITY returns the last value and not the value you expected. You should also know only IDENTITY PROPERTY is from ANSI SQL all others are functions Microsoft created to solve most known issues with IDENTITY PROPERTY.
Kind regards,
Gift Peddie
November 25, 2009 at 11:39 am
Gift Peddie (11/25/2009)
Dan Guzman - Not the MVP (11/25/2009)
wouldn't a rollback also have removed the records inserted? The current coulmn value should have gone down as well, I would think.That is the issue it removes the values but not the IDENTITY associated with the value that is the reason SCOPE_IDENTITY returns the last value and not the value you expected. You should also know only IDENTITY PROPERTY is from ANSI SQL all others are functions Microsoft created to solve most known issues with IDENTITY PROPERTY.
Thanks for the clarification, though I think this is the opposite of my problem. My IDENTITY value is less than the current column value. A rollback that doesn't reseed would leave the IDENTITY value higher than the current column value.
Dan
November 25, 2009 at 11:44 am
Dan Guzman - Not the MVP (11/25/2009)
Gift Peddie (11/25/2009)
Dan Guzman - Not the MVP (11/25/2009)
wouldn't a rollback also have removed the records inserted? The current coulmn value should have gone down as well, I would think.That is the issue it removes the values but not the IDENTITY associated with the value that is the reason SCOPE_IDENTITY returns the last value and not the value you expected. You should also know only IDENTITY PROPERTY is from ANSI SQL all others are functions Microsoft created to solve most known issues with IDENTITY PROPERTY.
Thanks for the clarification, though I think this is the opposite of my problem. My IDENTITY value is less than the current column value. A rollback that doesn't reseed would leave the IDENTITY value higher than the current column value.
Dan
IDENTITY is a PROPERTY it is not the column both are separate, ANSI SQL have defined IDENTITY Column but Microsoft have not implemented that so your column increment and the IDENTITY increment are not expected to be the same.
Kind regards,
Gift Peddie
November 25, 2009 at 1:25 pm
Dan,
The likely candidate is someone inserting a value using SET IDENTITY_INSERT ON that is too high.
A rollback should not have affected things, as you've noted. If you inserted 1,000 values with a current identity of 50,000 and rolled back, the current identity should move to 51,001, but your highest value would still be 50,000.
I have never heard of an issue with there being any corruption here. A DBCC RESEED could have changed things as well. My guess is that manual intervention somewhere broke things.
November 25, 2009 at 1:52 pm
Thanks Steve,
I'm starting to lean towards a reported bug in SQL Data Compare 8.0.1 that sets the RESEED value of a non-sync'd table to the SEED value of the 'Source' table and db. I'm not convinced, since I've been using the software for over a year now, and have never had this problem before, but I rarely do a sync back to the production db like I had done that morning. I'm usually sync'ing production down to Dev to run tests, but I had 4 specific records that where updating on the STAGE db, then pushed up to prod, and if it reseeded a table that was not part of the sync, to the STAGE table seed, then that would explain why the SEED was so low (since the Production db had new records added since the last sync of that table), and blew up at the next insert.
That's my version of the story, and I'm sticking to it, maybe.
Dan
November 25, 2009 at 2:18 pm
Dan,
That sounds like something that I'd be looking hard at. If you could repro, that would be interesting.
November 25, 2009 at 2:39 pm
The likely candidate is someone inserting a value using SET IDENTITY_INSERT ON that is too high.
A rollback should not have affected things, as you've noted. If you inserted 1,000 values with a current identity of 50,000 and rolled back, the current identity should move to 51,001, but your highest value would still be 50,000.
I have never heard of an issue with there being any corruption here. A DBCC RESEED could have changed things as well. My guess is that manual intervention somewhere broke things.
I actually think the thread was miss posted in the data corruption forum and if more than one million is to be added I would use BIGint. In 2005/8 the OUTPUT clause in the INSERT statement could show what may be the reason for the issue.
I'm starting to lean towards a reported bug in SQL Data Compare 8.0.1 that sets the RESEED value of a non-sync'd table to the SEED value of the 'Source' table and db. I'm not convinced, since I've been using the software for over a year now, and have never had this problem before, but I rarely do a sync back to the production db like I had done that morning.
If the value is important in SQL Server 2005/8 you could use the OUTPUT clause.
http://msdn.microsoft.com/en-us/library/ms177564.aspx
Kind regards,
Gift Peddie
November 25, 2009 at 3:10 pm
Thanks Gift Peddie, I'll keep an eye on it.
We're only inserting a few thousand records at a time, and int is good to over 2 billion, so I think we are OK for the next thousand years or more.:-)
December 7, 2009 at 8:56 am
December 7, 2009 at 9:10 am
I'm not sure I understand the question, but here is a stab.
DBCC CHECKIDENT (YourTableName, RESEED)
This will reset the seed value in your identity column to the current maximum value in the field.
DBCC CHECKINDENT (YourTableName, RESEED, 0)
This will reset the seed value in your table to 0, or any other number you choose to type in that third place.
BOL CHECKIDENT
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply