Database Corruption Part 3 :: Differences between CHECKTABLE and CHECKDB

  • Article

In this post, we would have a look at the differences between DBCC CHECKTABLE and DBCC CHECKDB. We would not go deep into how each works – that has already been very beautifully documented by the SQL Server Storage Engine Team in their blog posts; we would rather have a quick look at the differences between the two commands.

DBCC CHECKTABLE:

DBCC CHECKTABLE performs a physical consistency check on a single table or indexed view and all its non-clustered and XML indexes, unless the NOINDEX option is specified.

For the specified table, DBCC CHECKTABLE checks for the following:

  • Index, in-row, LOB, and row-overflow data pages are correctly linked.
  • Indexes are in their correct sort order.
  • Pointers are consistent.
  • The data on each page is reasonable, included computed columns.
  • Page offsets are reasonable.
  • Every row in the base table has a matching row in each non-clustered index, and vice-versa.
  • Every row in a partitioned table or index is in the correct partition.

DBCC CHECKTABLE uses an internal database snapshot to provide the transactional consistency that it must have to perform these checks.

If a snapshot cannot be created, or TABLOCK is specified, DBCC CHECKTABLE acquires a shared table lock to obtain the required consistency. By default, DBCC CHECKTABLE performs parallel checking of objects. The degree of parallelism is automatically determined by the query processor. The maximum degree of parallelism is configured in the same manner as that of parallel queries. To restrict the maximum number of processors available for DBCC checking, use sp_configure.

DBCC CHECKDB:

In earlier versions of SQL Server, the values for the per-table and per-index row count and page counts could become incorrect. Under certain circumstances, one or more of these values could even become negative. DBCC CHECKDB, in the earlier versions, did not detect this issue. This is not a corruption of any data stored in the database, and the situation is typically addressed by running the DBCC UPDATEUSAGE command.

In SQL Server 2005, DBCC CHECKDB has been enhanced to detect when any one of these counts becomes negative. When detected, the DBCC CHECKDB output contains a warning and a recommendation to run DBCC UPDATEUSAGE to address the issue. Although it may appear as if upgrading the database to SQL Server 2005 has caused this issue, it was, in fact, present before the upgrade procedure. DBCC CHECKDB does not examine disabled indexes.

A useful summary of its operation is that internally DBCC CHECKDB:

  • Runs DBCC CHECKALLOC on the database.
  • Runs DBCC CHECKTABLE on every table and view in the database.
  • Validates the Service Broker data in the database.
  • Runs DBCC CHECKCATALOG on the database.
  • Validates the contents of every indexed view in the database.

This means that DBCC CHECKALLOC, DBCC CHECKTABLE or DBCC CHECKCATALOG do not have to be run separately from DBCC CHECKDB. See the descriptions of those commands in Books OnLine for more detailed information about the checks performed.

Because the Resource database is accessible only in single-user mode, the DBCC CHECKDB command cannot be run on it directly. However, when DBCC CHECKDB is executed against the master database, a second CHECKDB is also run internally on the Resource database. This means that DBCC CHECKDB can return extra results. The command returns extra result sets when no options are set, or when either the PHYSICAL ONLY or ESTIMATE ONLY option is set.

DBCC CHECKDB uses an internal database snapshot to provide the transactional consistency that it must have to perform these checks. If a snapshot cannot be created, or TABLOCK is specified, DBCC CHECKDB acquires locks to obtain the required consistency. In this case, an exclusive database lock is required to perform the allocation checks, and shared table locks are required to perform the table checks.

Additional reading from the blogs posted by the SQL Server Storage Engine Team:
How long will CHECKDB take to run?
Consistency checking options for a VLDB
How does CHECKDB get a consistent view of the database?
What does CHECKDB really do?
Can repair fix everything?

Disclaimer: All information provided here is my personal opinion and is neither verified nor approved by Microsoft before it is published. All information, and code samples, if any, is provided "AS IS" with no warranties and confers no rights.