Tuesday, 19 April 2011

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:
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
nonclustered, unique, unique key located on PRIMARY
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])
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,
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.


  1. Your articles are very helpful and filled with good amount of technical information as always.

    It is always a joy to read your articles with such vivid style and simple language and no bloat.

    Keep those articles comming!