DBCC CHECKTABLE

Transact-SQL Reference

Transact-SQL Reference

DBCC CHECKTABLE

Checks the integrity of the data, index, text, ntext, and image pages for the specified table or indexed view.

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

Arguments

'table_name' | 'view_name'

Is the table or indexed view for which to check data page integrity. Table or view names must conform to the rules for identifiers. For more information, see Using Identifiers.

NOINDEX

Specifies that nonclustered indexes for nonsystem tables should not be checked.

REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD

Specifies that DBCC CHECKTABLE repair the found errors. The database must be in single-user mode to use a repair option and can be one of the following.

Value Description
REPAIR_ALLOW_DATA_LOSS Performs all repairs done by REPAIR_REBUILD and includes allocation and deallocation of rows and pages for correcting allocation errors, structural row or page errors, and deletion of corrupted text objects. These repairs can result in some data loss. The repair may be done under a user transaction to allow the user to roll back the changes made. If repairs are rolled back, the database will still contain errors and should be restored from a backup. If a repair for an error has been skipped due to the provided repair level, any repairs that depend on the repair are also skipped. After repairs are completed, back up the database.
REPAIR_FAST Performs minor, nontime-consuming repair actions such as repairing extra keys in nonclustered indexes. These repairs can be done quickly and without risk of data loss.
REPAIR_REBUILD Performs all repairs done by REPAIR_FAST and includes time-consuming repairs such as rebuilding indexes. These repairs can be done without risk of data loss.

index_id

Is the index identification (ID) number for which to check data page integrity. If an index_id is specified, DBCC CHECKTABLE checks only that index.

WITH

Specifies options on the number of error messages returned, locks obtained, or estimating tempdb requirements. If neither ALL_ERRORMSGS nor NO_INFOMSGS is specified, Microsoft® SQL Server™ returns all error messages.

ALL_ERRORMSGS

Displays all error messages. If not specified, SQL Server displays a maximum of 200 error messages per table. Error messages are sorted by object ID.

NO_INFOMSGS

Suppresses all informational messages and the report of space used.

TABLOCK

Causes DBCC CHECKTABLE to obtain a shared table lock.

ESTIMATE ONLY

Displays the estimated amount of tempdb space needed to run DBCC CHECKTABLE with all of the other specified options.

PHYSICAL_ONLY

Limits the checking to the integrity of the physical structure of the page and record headers, and to the consistency between the pages' object ID and index ID and the allocation structures. Designed to provide a low overhead check of the physical consistency of the database, this check also detects torn pages and common hardware failures that can compromise a user's data. PHYSICAL_ONLY always implies NO_INFOMSGS and is not allowed with any of the repair options.

Remarks

DBCC CHECKTABLE performs a physical consistency check on tables and indexed views. The NOINDEX option, used only for backward compatibility, also applies to indexed views.

For the specified table, DBCC CHECKTABLE checks that:

  • Index and data pages are correctly linked.

  • Indexes are in their proper sort order.

  • Pointers are consistent.

  • The data on each page is reasonable.

  • Page offsets are reasonable.

DBCC CHECKTABLE checks the linkages and sizes of text, ntext, and image pages for the specified table. However, DBCC CHECKTABLE does not verify the consistency of all the allocation structures in the database. Use DBCC CHECKALLOC to do this verification.

DBCC CHECKTABLE does not acquire a table lock by default. Instead, it acquires a schema lock that prevents meta data changes but allows changes to the data. The DBCC statement collects information, then scans the log for any additional changes made, merging the two sets of information together to produce a consistent view of the data at the end of the scan.

When the TABLOCK option is specified, DBCC CHECKTABLE acquires a shared table lock. This allows more detailed error messages for some classes of errors and minimizes the amount of tempdb space required by avoiding the use of transaction log data.

To perform DBCC CHECKTABLE on every table in the database, use DBCC CHECKDB.

By default, DBCC CHECKTABLE performs parallel checking of objects. The degree of parallelism is determined automatically by the query processor. The maximum degree of parallelism is configured in the same manner as that of parallel queries. Use the sp_configure system stored procedure to restrict the maximum number of processors available for DBCC checking. 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.

Result Sets

DBCC CHECKTABLE returns this result set (same result set is returned if you specify only the table name or if you provide any of the options); this example specifies the authors table in the pubs database (values may vary):

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

DBCC CHECKTABLE returns this result set when the ESTIMATEONLY option is specified.

Estimated TEMPDB space needed for CHECKTABLES (KB) 
-------------------------------------------------- 
2

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Permissions

DBCC CHECKTABLE permissions default to members of the sysadmin fixed server role or the db_owner fixed database role, or the table owner, and are not transferable.

Examples
A. Check a specific table

This example checks the data page integrity of the authors table.

DBCC CHECKTABLE ('authors')
GO
B. Check the table without checking nonclustered indexes

This example checks the data page integrity of the authors table without checking nonclustered indexes.

DBCC CHECKTABLE ('authors') WITH PHYSICAL_ONLY
GO
C. Check a specific index

This example checks a specific index, obtained by accessing sysindexes.

USE pubs
DECLARE @indid int
SELECT @indid = indid 
FROM sysindexes
WHERE id = OBJECT_ID('authors') AND name = 'aunmind'
DBCC CHECKTABLE ('authors', @indid)
GO

See Also

DBCC

Features Supported by the Editions of SQL Server 2000

How to configure the number of processors available for parallel queries (Enterprise Manager)

Table and Index Architecture