DBCC CHECKTABLE (Transact-SQL)

Syntax

DBCC CHECKTABLE 
(
    table_name | view_name
    [ , { NOINDEX | index_id }
     |, { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } 
    ] 
)
    [ WITH 
        { ALL_ERRORMSGS ]
          [ , EXTENDED_LOGICAL_CHECKS ] 
          [ , 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 repairs that have no possibility of data loss. This can include quick repairs, such as repairing missing rows in non-clustered indexes, and more time-consuming repairs, such as rebuilding an index.

    REPAIR_REBUILD does not repair errors involving FILESTREAM data.

  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 CHECKTABLE 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 CHECKTABLE with this option.

• ALL_ERRORMSGS
  Displays an unlimited number of errors. All error messages are displayed by default. Specifying or omitting this option has no effect.

• EXTENDED_LOGICAL_CHECKS
  If the compatibility level is 100 (SQL Server 2008) or higher, performs logical consistency checks on an indexed view, XML indexes, and spatial indexes, where present.

  For more information, see "Performing Logical Consistency Checks on Indexes" in the "Remarks" section later in this topic.

• 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. 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.

  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.

  Note

  Specifying PHYSICAL_ONLY causes DBCC CHECKTABLE to skip all checks of FILESTREAM data.

• 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 and later, 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.

Remarks

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 nonclustered index, and vice-versa.

• Every row in a partitioned table or index is in the correct partition.

• Link-level consistency between the file system and table when storing varbinary(max) data in the file system using FILESTREAM.

Performing Logical Consistency Checks on Indexes

Internal Database Snapshot

Checking and Repairing FILESTREAM Data

Checking Objects in Parallel

Understanding DBCC Error Messages

Error Reporting

Resolving Errors

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.

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

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

B. Performing a low-overhead check of the table

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

C. Checking a specific index

USE AdventureWorks2008R2;
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

Concepts

Other Resources