April 12, 2006 at 3:21 am
Hi All,
I have a DBCC CHECKDB job that runs each night on our databases, and recently it has started picking up on some errors. The following morning, when seeing that an error was found, I run a statement (create index blah on wotsit(col1,col2) with DROP_EXISTING) and then run DBCC CHECKDB again. The error is gone as expected.
However, the next night, the automatic DBCC runs again, and the exact same error is back again - same key's, same data row and everything.
The same thing happens for a few nights, then the error goes away for good.
Can anyone shed any light on why this may be happening?
Here is one of the errors - this one broke on four consecutive nights....
Thanks in advance,
Martin
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Table 'bookings' (ID 437576597). Missing or invalid key in index 'booker_number' (ID 5) for the row:
[Microsoft][ODBC SQL Server Driver][SQL Server]Data row (1:115041:0) identified by (RID = (1:115041:0) booking_number = 1535600) has index values (booker_number = 1367864 and eventnumber = 1317398 and booking_number = 1535600).
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Table 'bookings' (ID 437576597). Missing or invalid key in index 'booker_number' (ID 5) for the row:
[Microsoft][ODBC SQL Server Driver][SQL Server]Data row (1:115041:1) identified by (RID = (1:115041:1) booking_number = 1535601) has index values (booker_number = 1367864 and eventnumber = 1317398 and booking_number = 1535601).
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Table 'bookings' (ID 437576597). Missing or invalid key in index 'booker_number' (ID 5) for the row:
[Microsoft][ODBC SQL Server Driver][SQL Server]Data row (1:115041:2) identified by (RID = (1:115041:2) booking_number = 1535602) has index values (booker_number = 1367864 and eventnumber = 1317398 and booking_number = 1535602).
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Table 'bookings' (ID 437576597). Missing or invalid key in index 'booker_number' (ID 5) for the row:
[Microsoft][ODBC SQL Server Driver][SQL Server]Data row (1:115041:3) identified by (RID = (1:115041:3) booking_number = 1535603) has index values (booker_number = 1367864 and eventnumber = 1317398 and booking_number = 1535603).
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Table 'bookings' (ID 437576597). Missing or invalid key in index 'booker_number' (ID 5) for the row:
[Microsoft][ODBC SQL Server Driver][SQL Server]Data row (1:115041:4) identified by (RID = (1:115041:4) booking_number = 1535604) has index values (booker_number = 1367864 and eventnumber = 1317398 and booking_number = 1535604).
April 12, 2006 at 8:36 am
I would look at the disk subsystem to see if any errors are being thrown on it. A lot of times checkdb errors are thrown because of impending hardware problems.
Tom
April 12, 2006 at 8:53 am
I might also try rebuilding the clustered index and seeing if you can rebuild the structure of the table.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 12, 2006 at 9:01 am
Many thanks both of you for your replies. I will rebuild the clustered index, and check the disk subsystem and then monitor to see what happens.
Martin
April 13, 2006 at 7:21 am
I've seen this type of problem before, where a DBCC check reported errors that didn't really exist.
We eventually traced the problem to firmware on our SAN (MSA1000), which required an upgrade.
If the problem keeps happening after you do the other things that have been mentioned here, I'd suggest trying the following:
instead of rebuilding the index, try flushing the buffer cache by doing this:
DBCC DropCleanBuffers
and then re-run the DBCC CheckDB. Please keep in mind that the DBCC DropCleanBuffers command can have a significant impact on performance, similar to performance right after SQL starts up, so you want to make sure that the time is appropriate.
If this makes the problem go away, I'd look to disk controllers/firmware/drivers.
good luck!
jg
April 18, 2006 at 3:51 pm
Thanks for the reply, Jeff. I have so far rebuilt the clustered index as suggested above, and so far (fingers crossed) there have not been any more occurances. However, I will keep your suggestion in mind just in case it does come back.
Best regards,
Martin
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply