Dear Friends,
SQL Server 2000 has already reached end of support state but many of us still see them on our environments. As long as we do not migrate these instances to a supported/higher edition, we need to support them as database admins. This is not an exception in my case also i.e. most of SQL Server 2000 instances are running business critical applications and client is happy with the way it is unless we land up in any issues with any of the databases hosted on top of it.
Like other environments we do have DBCC CHECKDB jobs scheduled for our SQL Server 2000 instances with no exceptions. One fine day our daily job report alerted us that CHECKDB failed for a particular database. On checking error message I observed that it failed with following error;
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page with latch type SH. sysindexes failed.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
This is something we did not see anytime till the time it landed up in our inbox that morning and as per initial look seems to be system table index leaf-page corruption. One of the important checks in CHECKDB is to check critical system tables that hold storage engine metadata. In SQL Server 2000 they are sysindexes, sysobjects and syscolumns. These tables have to be checked first because the metadata they contain allow us to access all the other tables and indexes in the database.
I thought of checking this further and have a proper look. From error message it is clear that there is some issue with File ID 1, Page No.4354 and we also got to know name of the database from our report. It is important to know table name and any other details which could help us in troubleshooting the issue in a better way. I decided to delve into this error and decode more details with DBCC PAGE with printopt 3 to interpret page header plus detailed per-row interpretation. To achieve this, I had to turn on Trace Flag 3604 as this force the output to the client (SSMS/Query Analyzer results pane etc.). So I started off with following;
DBCC TRACEON(3604)
DBCC PAGE(‘YourDatabaseName’,1,4354,3)
AND output shown as following;
Server: Msg 7987, Level 22, State 1, Line 1
A possible database consistency problem has been detected on database ‘ YourDatabaseName ‘. DBCC CHECKDB and DBCC CHECKCATALOG should be run on database ‘ YourDatabaseName ‘.
6F456E15: 30706d64 01000400 00010000 3133003e dmp0……..>.31
Time to run DBCC CHECKCATALOG on respective database and following is the output;
Server: Msg 2513, Level 16, State 8, Line 1
Table error: Object ID 94623380 (object ‘YourTableName’) does not match between ‘SYSOBJECTS’ and ‘SYSINDEXES’.
Server: Msg 7987, Level 22, State 1, Line 1
A possible database consistency problem has been detected on database ‘ YourDatabaseName ‘. DBCC CHECKDB and DBCC CHECKCATALOG should be run on database ‘ YourDatabaseName ‘.
DBCC results for ‘ YourDatabaseName ‘.
Yes, I got to know the table name now and being too optimistic thought to run Select * from YourTableName but before I do that, I tried to expand database and have check into the table but it thrown following error to me;
Anyway, time to run Select query on the table in question and here is the output;
Server: Msg 602, Level 21, State 16, Line 1
Could not find row in sysindexes for database ID 6, object ID 94623380, index ID -1. Run DBCC CHECKTABLE on sysindexes.
Fine, ran DBCC CHECKTABLE(sysindexes) and following is something I received as output;
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:4354) with latch type SH. sysindexes failed.
Server: Msg 8940, Level 16, State 1, Line 1
Table error: Object ID 2, index ID 0, page (1:4354). Test (IsAligned (m_freeData)) failed. Address 0x1b02 is not aligned.
CHECKTABLE found 0 allocation errors and 1 consistency errors in table ‘sysindexes’ (object ID 2).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Actually, as part of critical system table check (discussed earlier) DBCC is designed to verify that any internal query, metadata don’t throw any errors. Each of these system tables i.e. sysindexes, sysobjects and syscolumns has a clustered index and this as part of this table check it is ensured that leaf level data pages are in safe state. To achieve this, one of the critical checks is to read and latch the page to make sure there aren’t any issues with IO such as a torn page etc. If any of the pages at the leaf-level of these indexes are corrupt, we cannot repair them. Repairing would actually mean de-allocating the page, clearing out the important metadata for potentially number of user tables and so effectively deleting all of these objects. That’s obviously an unacceptable repair for anyone to agree and allow such data loss.
Any error found at this stage cannot be repaired so we must restore from a backup.
Regards
Kanchan Bhattacharyya
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
