DBCC CheckTable

Being day seven of the DBCC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC CHECKTABLE.

Description:

DBCC CheckTable is used to check the structure of a table to verify the integrity of every data page associated with that table, and all of the indexes associated with that table. If you have used DBCC CheckDB, and a problem has been shown with a table, you can use DBCC CheckTable to attempt to correct the problem.

DBCC CHECKTABLE Syntax:

dbcc checktable
(
    { 'table_name' | 'view_name' }
    [ , NOINDEX
    | index_id
    | { REPAIR_ALLOW_DATA_LOSS
    | REPAIR_FAST
    | REPAIR_REBUILD
    } ]
)
    [ WITH
        {
            [ ALL_ERRORMSGS ]
            [ , [ NO_INFOMSGS ] ]
            [ , [ TABLOCK ] ]
            [ , [ ESTIMATEONLY ] ]
            [ , [ PHYSICAL_ONLY ] ]
            [ , [ EXTENDED_LOGICAL_CHECKS  ] ]
        }
    ]

Parameters:

The REPAIR_FAST parameter is only there for reverse compatibility and does nothing.  This was removed in SQL Server 2005.

Example:

The following example….

DBCC CheckTable(Departments);

Which produces the following output indicating success with removal of the corruption.

DBCC results for 'Departments'.
There are 4 rows in 1 pages for object "Departments".
DBCC execution completed. If DBCC printed error messages, contact your 
system administrator.

That is the best case scenario, everything worked fine.   Now we will take a look at what to do if there is corruption.

Example with Corruption:

DBCC CheckTable(Departments);

Produces the following erorr:

Msg 8951, Level 16, State 1, Line 1
Table error: table 'Departments' (ID 245575913). Data row does not 
have a matching index row in the index 'DepartmentsNonClustered' (ID 2). 
Possible missing or invalid keys for the index row matching:

Msg 8955, Level 16, State 1, Line 1
Data row (1:231:0) identified by (id = 1) with index values 'department = 
'aaaping' and parent = NULL and archived = 0 and id = 1'.

Msg 8952, Level 16, State 1, Line 1
Table error: table 'Departments' (ID 245575913). Index row in index 
'DepartmentsNonClustered' (ID 2) does not match any data row. Possible 
extra or invalid keys for:

Msg 8956, Level 16, State 1, Line 1
Index row (1:273:0) with values (department = 'Camping' and parent = NULL
 and archived = 0 and id = 1) pointing to the data row identified by 
(id = 1).
DBCC results for 'Departments'.

There are 4 rows in 1 pages for object "Departments".
CHECKTABLE found 0 allocation errors and 2 consistency errors in table
 'Departments' (object ID 245575913).
repair_rebuild is the minimum repair level for the errors found by DBCC
 CHECKTABLE (dbcc_corruption.dbo.Departments).
DBCC execution completed. If DBCC printed error messages, contact your
 system administrator.

Indicating that the an index, the DepartmentsNonClustered is corrupt.

However when we query the table, all of the data looks fine.

So to fix the corrupt index, the best option is probably to just drop the index and recreate it. Lets give that a try:

DROP INDEX [DepartmentsNonClustered] ON [dbo].[Departments];
CREATE NONCLUSTERED INDEX [DepartmentsNonClustered] ON [dbo].[Departments]
(
 [department] ASC,
 [parent] ASC,
 [archived] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
       DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
       ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
GO

DBCC CheckTable(Departments);

Which produces the following output indicating success with removal of the corruption.

DBCC results for 'Departments'.
There are 4 rows in 1 pages for object "Departments".
DBCC execution completed. If DBCC printed error messages, contact your 
system administrator.

In this case we were lucky, the table itself wasn’t corrupt, it was just an index which was easy enough to drop and recreate. Had the table structure itself, or the clustered index been corrupt that may have required more drastic steps, either restoring from backup, or saving the data we could as shown in my DBCC CheckDB blog article from a few days ago.

Notes:

DBCC CheckAlloc along with DBCC CheckTable for every object in the database are called when DBCC CheckDB is run. Running DBCC CheckAlloc or DBCC CheckTable would be redundant after running DBCC CheckDB.

The REPAIR_FAST parameter is only there for reverse compatibility and does nothing.  This was removed in SQL Server 2005.

For more information see TSQL Wiki DBCC checktable.

DBCC Command month at SteveStedman.com is almost as much fun as building Robotic Dinosaurs.

For more details on DBCC CheckTable see the Database Corruption Challenge

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!