Error: Could not find row in sysindexes for database. 8966, 823 and 602
Long ago, not long ago.... No doubt that many of you might have gone through the error above within your SQL environment, also I see many forum posts out there to resolve the issue. The bottom line of this issue is Hardware and no other issue can contribute such a problem. So this is where DBA's role is enhanced to take care of SQL Server hardware too.
By going further into details recently System Operations & Developers complained about error they are receiving whenever tried to select data from a database:
Server: Msg 823, Level 24, State 2, Line 1
I/O error (bad page ID) detected during read at offset 0x00000000651000 in file 'DatabaseName.mdf'.
Connection Broken
Obviously our DBA team performed a health check and database maintenance by running, DBCC CHECKDB('DBname'). For the first time no errors were reported, but during the weekend scheduled jobs for database maintenace reported this spurious error:
Result:
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:1073) with latch type SH. sysindexes failed.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
also you might observe the following error within that SQL error log:
Server: Msg 602, Level 21, State 16, Line 1
Could not find row in sysindexes for database ID 87, object ID 1701581100, index ID -1. Run DBCC CHECKTABLE on sysindexes.
Problem averted from warnings to errors (more serious)!
First thing to check for whether any issues with the server hardware or the disk where this table-filegroup is located. If that instance is a SQL 2000 then further I would suggest to refer the BOL about error 8966 explanation and follow the actions prescribed error8966article.
Also I have seen and performed the action to repair the corrupted pages on the database by running DBCC Checkdb with option REPAIR_ALLOW_DATA_LOSS statement. As stated this will have affect on data loss and most of the times I haven't seen correcting the problem, back to old drawing board.
So by default restoring from the last good known backup is only way out, but is it worth to replace whole database for the sake of this single-page corruption. You could try a bit shortcut here by not restoring entire database, rather RESTORE as a seperate DATABASE and then use DTS or BCP to import data to old table from the newly restore database table. Sometimes even this will not let you to drop the corrupted page table, instead will throw error 602 as referred above. So if the issue is highlight towards SYSINDEXES table then you could try running DBCC UPDATEUSAGE(SYSINDEXES) to correct the mismatched linkages, a rarest chance to resolve this issue but worth trying it.
So by taking this long run approach you could avoid this error and also by replacing the offending hardware part as the main problem here is hardware that contributes a bad page in sysindexes (a bad page on the disk). As a matter of fact that SQL Server can't repair this page so you have no choice but to restore from backup or extract the data into a new db on different hardware. So other than this I don't have any further solution to offer, as we do get such issues within our environment and keeping them to move to better hardware slowly.
Within my investigation saga for this problem I have found few resources that will help a lot to identify other root causes too, refer the following:
KBA838765 - see whether your hardware/operating system falls under this category.
CHECKDB-series1 & CHECKDB-series2 an excellent resource from Paul Randall to understand how it happens and to resolve (helped me a lot in the same scenario). These blog posts covers SQL Server 2000 & 2005 too, but mainly focus is towards 2005 version.