Share via

DBCC CHECKTABLE (Transact-SQL)

  • Article

Checks the integrity of all the pages and structures that make up the table or indexed view.

Topic link iconTransact-SQL Syntax Conventions

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 | DATA_PURITY } ] 
        }
    ]

Arguments

  • table_name | view_name
    Is the table or indexed view for which to run integrity checks. Table or view names must comply with the rules for identifiers.
  • NOINDEX
    Specifies that intensive checks of nonclustered indexes for user tables should not be performed. This decreases the overall execution time. NOINDEX does not affect system tables because the integrity checks are always performed on all system table indexes.
  • index_id
    Is the index identification (ID) number for which to run integrity checks. If index_id is specified, DBCC CHECKTABLE runs integrity checks only on that index, together with the heap or clustered index.

  • REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
    Specifies that DBCC CHECKTABLE repair the found errors. To use a repair option, the database must be in single-user mode.

    • REPAIR_ALLOW_DATA_LOSS
      Tries to repair all reported errors. These repairs can cause some data loss.
    • REPAIR_FAST
      Syntax is maintained for backward compatibility only. No repair actions are performed.
    • REPAIR_REBUILD
      Performs both minor, nontime-consuming repair actions such as repairing extra keys in nonclustered indexes and time-consuming repairs such as rebuilding indexes. These repairs can be performed without risk of data loss.

    Note

    Use the REPAIR options only as a last resort. To repair errors, we recommend restoring from a backup. Repair operations do not consider any of the constraints that may exist on or between tables. If the specified table is involved in one or more constraints, we recommend running DBCC CHECKCONSTRAINTS after a repair operation. If you must use REPAIR, run DBCC CHECKDB without a repair option to find the repair level to use. If you are going to use the REPAIR_ALLOW_DATA_LOSS level, we recommend that you back up the database before you run DBCC CHECKDB with this option.

  • ALL_ERRORMSGS
    Displays an unlimited number of errors. In SQL Server 2005 Service Pack 3 (SP3), all error messages are displayed by default. Specifying or omitting this option has no effect. In earlier versions of SQL Server, only the first 200 error messages for each object are displayed if ALL_ERRORMSGS is not specified.
  • NO_INFOMSGS
    Suppresses all informational messages.
  • TABLOCK
    Causes DBCC CHECKTABLE to obtain a shared table lock instead of using an internal database snapshot. TABLOCK will cause DBCC CHECKTABLE to run faster on a table under heavy load, but decreases the concurrency available on the table while DBCC CHECKTABLE is running.
  • ESTIMATEONLY
    Displays the estimated amount of tempdb space needed to run DBCC CHECKTABLE with all the other specified options.

  • PHYSICAL_ONLY
    Limits the checking to the integrity of the physical structure of the page, record headers and the physical structure of B-trees. Designed to provide a small overhead check of the physical consistency of the table, this check can also detect torn pages, and common hardware failures that can compromise data. In SQL Server 2005, a full run of DBCC CHECKTABLE may take considerably longer than in earlier versions. This behavior occurs because of the following reasons:

    • The logical checks are more comprehensive.
    • Some of the underlying structures to be checked are more complex.
    • Many new checks have been introduced to include the new features in SQL Server 2005.

    Therefore, using the PHYSICAL_ONLY option may cause a much shorter run-time for DBCC CHECKTABLE on large tables and is therefore recommended for frequent use on production systems. We still recommend that a full run of DBCC CHECKTABLE be performed periodically. The frequency of these runs depends on factors specific to individual businesses and production environments. PHYSICAL_ONLY always implies NO_INFOMSGS and is not allowed with any one of the repair options.

  • DATA_PURITY
    Causes DBCC CHECKTABLE to check the table for column values that are not valid or out-of-range. For example, DBCC CHECKTABLE detects columns with date and time values that are larger than or less than the acceptable range for the datetime data type; or decimal or approximate-numeric data type columns with scale or precision values that are not valid.

    For databases created in SQL Server 2005, column-value integrity checks are enabled by default and do not require the DATA_PURITY option. For databases upgraded from earlier versions of SQL Server, you can use DBCC CHECKTABLE WITH DATA_PURITY to find and correct errors on a specific table; however, column-value checks on the table are not enabled by default until DBCC CHECKDB WITH DATA_PURITY has been run error free on the database. After this, DBCC CHECKDB and DBCC CHECKTABLE check column-value integrity by default.

    Validation errors reported by this option cannot be fixed by using DBCC repair options. For information about manually correcting these errors, see Knowledge Base article 923247: Troubleshooting DBCC error 2570 in SQL Server 2005.

    If PHYSICAL_ONLY is specified, column-integrity checks are not performed.

Result Sets

DBCC CHECKTABLE returns the following result set. The same result set is returned if you specify only the table name or any of the options.

DBCC results for 'HumanResources.Employee'.
There are 288 rows in 13 pages for object 'Employee'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC CHECKTABLE returns the following result set if the ESTIMATEONLY option is specified:

Estimated TEMPDB space needed for CHECKTABLES (KB) 
-------------------------------------------------- 
21
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Remarks

DBCC CHECKTABLE performs consistency checks on a single table or indexed view and all its nonclustered and XML indexes, unless the NOINDEX option is specified. To perform DBCC CHECKTABLE on every table in the database, use DBCC CHECKDB.

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, including computed columns.
  • Page offsets are reasonable.
  • Every row in the base table has a matching row in each nonclustered index, and vice-versa.
  • Every row in a partitioned table or index is in the correct partition.

Internal Database Snapshot

DBCC CHECKTABLE uses an internal database snapshot to provide the transactional consistency that it must have to perform these checks. For more information, see Understanding Sparse File Sizes in Database Snapshots and the "DBCC Internal Database Snapshot Usage" section in DBCC (Transact-SQL).

If a snapshot cannot be created, or TABLOCK is specified, DBCC CHECKTABLE acquires a shared table lock to obtain the required consistency.

Note

If DBCC CHECKTABLE is run against tempdb, it must acquire a shared table lock. This is because, for performance reasons, database snapshots are not available on tempdb. This means that the required transactional consistency cannot be obtained.

Checking Objects in Parallel

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. For more information, see max degree of parallelism Option.

Parallel checking can be disabled by using trace flag 2528. For more information, see Trace Flags (Transact-SQL).

Note

During a DBCC CHECKTABLE operation, the bytes that are stored in a byte-ordered user-defined type column must be equal to the computed serialization of the user-defined type value. If this is not true, the DBCC CHECKTABLE routine will report a consistency error.

Understanding DBCC Error Messages

After the DBCC CHECKTABLE command finishes, a message is written to the SQL Server error log. If the DBCC command successfully executes, the message indicates a successful completion and the amount of time that the command ran. If the DBCC command stops before completing the check because of an error, the message indicates the command was terminated, a state value, and the amount of time the command ran. The following table lists and describes the state values that can be included in the message.

State Description

0

Error number 8930 was raised. This indicates a metadata corruption that caused the DBCC command to terminate.

1

Error number 8967 was raised. There was an internal DBCC error.

2

A failure occurred during emergency mode database repair.

3

This indicates a metadata corruption that caused the DBCC command to terminate.

4

An assert or access violation was detected.

5

An unknown error occurred that terminated the DBCC command.

Error Reporting

In SQL Server 2005 Service Pack 1 (SP1), a small dump file (SQLDUMPnnnn.txt) is created in the SQL Server LOG directory whenever DBCC CHECKTABLE detects a corruption error. When the Feature Usage data collection and Error Reporting features are enabled for the instance of SQL Server, the file is automatically forwarded to Microsoft. The collected data is used to improve SQL Server functionality. For more information, see Error and Usage Report Settings.

The dump file contains the results of the DBCC CHECKTABLE command and additional diagnostic output. The file has restricted discretionary access-control lists (DACLs). Access is limited to the SQL Server service account and members of the sysadmin role. By default, the sysadmin role contains all members of the Windows BUILTIN\Administrators group and the local administrator's group. The DBCC command does not fail if the data collection process fails.

Resolving Errors

If DBCC CHECKTABLE reports any errors, we recommend restoring the database from the database backup instead of running REPAIR with one of the REPAIR options. If no backup exists, running REPAIR can correct the errors that are reported. The REPAIR option to use is specified at the end of the list of reported errors. However, that correcting the errors by using the REPAIR_ALLOW_DATA_LOSS option might require that some pages, and therefore data, be deleted.

The repair can be performed under a user transaction to allow the user to roll back the changes that have been made. If repairs are rolled back, the database will still contain errors and must be restored from a backup. After you have completed all repairs, back up the database.

Permissions

User must own the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

Examples

A. Checking a specific table

The following example checks the data page integrity of the HumanResources.Employee table in the AdventureWorks database.

USE AdventureWorks;
GO
DBCC CHECKTABLE ("HumanResources.Employee");
GO

B. Performing a low overhead check of the table

The following example performs a low overhead check of the Employee table in the AdventureWorks database.

USE AdventureWorks;
GO
DBCC CHECKTABLE ("HumanResources.Employee") WITH PHYSICAL_ONLY;
GO

C. Checking a specific index

The following example checks a specific index, obtained by accessing sys.indexes.

USE AdventureWorks;
GO
DECLARE @indid int;
SET @indid = (SELECT index_id 
              FROM sys.indexes
              WHERE object_id = OBJECT_ID('Production.Product')
                    AND name = 'AK_Product_Name');
DBCC CHECKTABLE ("Production.Product", @indid);

See Also

Reference

DBCC (Transact-SQL)

Other Resources

Table and Index Architecture
Troubleshooting DBCC Errors on Indexed Views

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 November 2008
New content:
  • In the definition of ALL_ERRORMSGS, described new functionality in SP3.

12 December 2006
New content:
  • Added the DATA_PURITY option to the Syntax and Arguments sections.

14 April 2006
New content:
  • In the Remarks section, added the subsection "Error Reporting." This section describes new functionality in SP1.

5 December 2005
New content:
  • Added user-defined type note.
Changed content:
  • Corrected the REPAIR_FAST definition. The option does not perform repair actions.
  • Corrected the syntax.
  • Corrected example C.