My encounters with DBCC CHECKDB - 1

After recovering from a disk crash on one of the production SQL Server 2000 servers, I ran DBCC CHECKDB on every database to ensure we don’t have any errors. It turned out otherwise, and we did have 2 consistency errors reported.

The part of DBCC CHECKDB output containing the error is copied below:

Server: Msg 8928, Level 16, State 1, Line 1 Object ID 1993058136, index ID 2: Page (6:23238457) could not be processed. See other errors for details. Server: Msg 8944, Level 16, State 1, Line 1 Table error: Object ID 1993058136, index ID 2, page (6:23238457), row 10. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 28416 and 37.

And the last part of CHECKDB output, summarized as below:

CHECKDB found 0 allocation errors and 2 consistency errors in database 'MIRA_OTHERS_CONTENT_DB'. repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (MIRA_OTHERS_CONTENT_DB ). DBCC execution completed. If DBCC printed error messages, contact your system administrator.

To start with, here is some info pertaining to SQL Server, using SELECT @@VERSION

Microsoft SQL Server  2000 - 8.00.2055 (Intel X86)   Dec 16 2008 19:46:53   Copyright (c) 1988-2003 Microsoft Corporation  Workgroup Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

The database involved is called: MIRA_OTHERS_CONTENT_DB;

Using the object ID value from the above output, I could find out the table on which errors were reported.

SELECT OBJECT_NAME (1993058136) as TableName

TableName: Docs

Going back to output above, it was obvious that the error was more precisely on an index, index id 2. Using this information and the object id, one could easily identify the index name affected:

USE MIRA_OTHERS_CONTENT_DB;

SELECT NAME AS IndexName from SYSINDEXES WHERE ID = 1993058136 and INDID = 2;

Returned, IndexName: Docs_IdUnique

Next, I ran SP_HELPINDEX Docs, for better understanding of the index definition.

Index Name	Index Description	Index Keys
Docs_IdUnique	nonclustered, unique, unique key located on PRIMARY	Id
Docs_PK	clustered, unique, primary key located on PRIMARY	SiteId, DirName, LeafName

Once the index name and the table name are known, all one has to do is to just re-create the non clustered index. We could use DBCC DBREINDEX or CREATE INDEX… DROP_EXISTIN to re-create the non-clustered index to eliminate corruption errors as below:

Option 1:

DBCC DBREINDEX ( ‘Docs’,’ Docs_IdUnique’)

Option 2:

CREATE UNIQUE   INDEX [Docs_IdUnique] ON [dbo].[Docs] ([Id])

WITH   FILLFACTOR = 90, DROP_EXISTING ON [PRIMARY]

Initially, I thought I could manually drop and create the index, as shown below

DROP INDEX Docs. Docs_IdUnique

But this statement failed, since the unique index is bound by the unique constraint. So then I thought I will drop the constraint. So I ran,

ALTER TABLE Docs DROP CONSTRAINT Docs_IdUnique

And to my surprise, I figure this index enforces the full-text key for Docs table

Cannot drop index 'Docs_IdUnique' because it enforces the full-text key for table 'Docs'.

At that time, I thought over, and figured I could use one of the above two options. If they did not work, I would do a drop and create the clustered index, which in SQL Server 2000 would force the non-clustered index to be rebuilt.

Note:  I would like to remind, that rebuild index behavior is different in SQL Server 2005 and 2008.