DBCC CheckTable for Database Integrity

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

2 thoughts on “DBCC CheckTable”

SK August 11, 2017 at 16:58


I dropped and re-created the affected index, but it still has consistency errors.
What do i do next?
Will my application or database be affected if i leave it for a day?

Thanks
1. SteveStedman August 12, 2017 at 16:45
  
  
  It seems unusual that if the errors were in an index and you dropped and recreated the index that the errors would still be there… Unless the errors are in the clustered index or heap (the table itself), and somehow the consistency error is being replicated into the index.
  
  As far as the question of will your application be affected if you leave it for a day? That can depend on a lot of things. Is the table/index with the corruption frequently used? Are you able to query information out of that table successfully? Are you attempting to insert or update rows in that table, and does that succeed with the corruption? The biggest and most important question may be do you know what caused the corruption, and is that problem still occurring? Do you have good backups from before the corruption was encountered?
  
  I have seen corruption that went undetected for more than a year and didn’t cause any problems, until it finally did. I have seen other times that corruption has caused the entire database to go offline and not be able to restart. It can really depend a lot on where the corruption is.
  
  I hope this helps. If you want some help we regularly offer consulting to resolve these type of corruption issues with no data loss. Contact me if you would like some help.
  
  -Steve Stedman