October 31, 2002 at 9:02 am
In our 90 gig SQL 7.0 database yesterday, 3 years after converting from FoxPro, the identity values on three tables became corrupted resulting in failed inserts due to primary key violations. This has never happened before.
Having never experienced this before, and in quite a rush, I first restarted the server which fixed the problem for @30 minutes and many inserts were confirmed. To resolve the second occurrence I restarted SQL which bought another @30 minutes. Then I ran dbcc checkident with reseed, checkIdent definetly confimed the values out of sync,and the problem has not re-curred. Over 20,000 inserts have since occurred on one of the affected tables.
I have queried all code for set identity_insert and found none. All developers are without admin rights so they should not be able to run identity_insert ad hoc.
Any ideas on what might have caused the corruption? Help would be very much appreciated.
Terry Duffy
October 31, 2002 at 11:26 am
Have you run dbcc checktable to check anything wrong on these three tables?
October 31, 2002 at 11:34 am
You can use dbcc checkident to reset the identity. This could have happened.
To fix things, find the max value and ensure the identity seed is set here. Then you can query and clean out the dups.
Steve Jones
October 31, 2002 at 12:01 pm
quote:
Have you run dbcc checktable to check anything wrong on these three tables?
I have run on dbcc checktable on two of the tables but not the third (43,000,000 rows). No errors were reported.
October 31, 2002 at 12:03 pm
quote:
You can use dbcc checkident to reset the identity. This could have happened.To fix things, find the max value and ensure the identity seed is set here. Then you can query and clean out the dups.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
I ran dbcc checkident with reseed, the values wer out of sync,and the problem has not re-curred. Yet!!
October 31, 2002 at 6:59 pm
May want to spot check this every day for awhile and see if anything changes.
Steve Jones
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply