quote:

---

Transact-SQL Reference


DBCC CHECKDB
Checks the allocation and structural integrity of all the objects in the specified database.

Syntax
DBCC CHECKDB
( 'database_name'
[ , NOINDEX
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD
} ]
) [ WITH { [ ALL_ERRORMSGS ]
[ , [ NO_INFOMSGS ] ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ]
}
]

Arguments
'database_name'

Is the database for which to check all object allocation and structural integrity. If not specified, the default is the current database. Database 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. NOINDEX decreases the overall execution time because it does not check nonclustered indexes for user-defined tables. NOINDEX has no effect on system tables, because DBCC CHECKDB always checks all system table indexes.

REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST| REPAIR_REBUILD

Specifies that DBCC CHECKDB repair the found errors. The given database_name 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.


WITH

Specifies options on the number of error messages returned, locks obtained, or estimating tempdb requirements.

ALL_ERRORMSGS

Displays an unlimited number of errors per object. If ALL_ERRORMSGS is not specified, displays up to 200 error messages for each object. Error messages are sorted by object ID, except for those messages generated from tempdb.

NO_INFOMSGS

Suppresses all informational messages (Severity 10) and the report of space used.

TABLOCK

Causes DBCC CHECKDB to obtain shared table locks. TABLOCK will cause DBCC CHECKDB to run faster on a database under heavy load, but decreases the concurrency available on the database while DBCC CHECKDB is running.

ESTIMATE ONLY

Displays the estimated amount of tempdb space needed to run DBCC CHECKDB with all of the other specified options. The check is not performed.

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 CHECKDB performs a physical consistency check on indexed views. The NOINDEX option, used only for backward compatibility, also applies to any secondary indexes on indexed views.

DBCC CHECKDB is the safest repair statement because it identifies and repairs the widest possible errors. If only allocation errors are reported for a database, execute DBCC CHECKALLOC with a repair option to repair these errors. However, to ensure that all errors, including allocation errors, are properly repaired, execute DBCC CHECKDB with a repair option rather than DBCC CHECKALLOC with a repair option.

DBCC CHECKDB validates the integrity of everything in a database. There is no need to run DBCC CHECKALLOC or DBCC CHECKTABLE if DBCC CHECKDB either is currently or has been recently executed.

DBCC CHECKDB performs the same checking as if both a DBCC CHECKALLOC statement and a DBCC CHECKTABLE statement were executed for each table in the database.

DBCC CHECKDB does not acquire table locks by default. Instead, it acquires schema locks that prevent meta data changes but allow changes to the data. The schema locks acquired will prevent the user from getting an exclusive table lock required to build a clustered index, drop any index, or truncate the table.

The DBCC statement collects information, and 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 CHECKDB acquires shared table locks. 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. The TABLOCK option will not block the truncation of the log and will allow the command to run faster.

DBCC CHECKDB checks the linkages and sizes of text, ntext, and image pages for each table, and the allocation of all the pages in the database.

For each table in the database, DBCC CHECKDB 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.
Errors indicate potential problems in the database and should be corrected immediately.

By default, DBCC CHECKDB 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
Whether or not any options (except for the NO_INFOMSGS or NOINDEX options) are specified, DBCC CHECKDB returns this result set for the current database, if no database is specified (values may vary):

DBCC results for 'master'.
DBCC results for 'sysobjects'.
There are 862 rows in 13 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 80 rows in 3 pages for object 'sysindexes'.
'...'
DBCC results for 'spt_provider_types'.
There are 23 rows in 1 pages for object 'spt_provider_types'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'master'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

IF the NO_INFOMSGS option is specified, DBCC CHECKDB returns this result set (message):

The command(s) completed successfully.

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

Estimated TEMPDB space needed for CHECKALLOC (KB)
-------------------------------------------------
13

(1 row(s) affected)

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

(1 row(s) affected)

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

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

Examples
A. Check both the current and the pubs database
This example executes DBCC CHECKDB for the current database and for the pubs database.

-- Check the current database.
DBCC CHECKDB
GO
-- Check the pubs database without nonclustered indexes.
DBCC CHECKDB ('pubs', NOINDEX)
GO

B. Check the current database, suppressing informational messages
This example checks the current database and suppresses all informational messages.

DBCC CHECKDB WITH NO_INFOMSGS
GO


See Also

Features Supported by the Editions of SQL Server 2000

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

Physical Database Architecture

sp_helpdb

System Tables

©1988-2000 Microsoft Corporation. All Rights Reserved.

---

quote:

---

Troubleshooting


DBCC CHECKDB Recommendations
In Microsoft® SQL Server™ 2000, you can run DBCC CHECKDB while users are using the database because of a change in the type of locks that DBCC CHECKDB holds on the database tables as it checks each one.

In SQL Server 7.0 and earlier, DBCC CHECKDB (which in turn runs DBCC CHECKTABLE and CHECKALLOC on each table in the database) used to hold shared locks (S) on the tables, thus blocking all data modification language (DML) statements.

In SQL Server 2000, DBCC CHECKDB holds a schema lock on the table to prevent meta data changes while the table is being checked, thus allowing DML statements but not any data definition language (DDL) statements on the tables being checked. This change provides greater flexibility as to when you can run DBCC CHECKDB because DBCC CHECKDB does not deny system usage completely to the users.

DBCC CHECKDB is a CPU- and disk-intensive operation. Each data page that requires checking must first be read from disk into memory. In addition, DBCC CHECKDB uses tempdb to do sorting.

If actively performing transactions while DBCC CHECKDB is running, the transaction log continues to grow because the DBCC command blocks log truncation until it has finished reading the log.

It is recommended that DBCC CHECKDB be run during hours when the load is light on the server. If DBCC CHECKDB is run during heavy peak usage time, expect a performance hit on the transaction throughput as well as DBCC CHECKDB completion time.

Recommendations for Good DBCC Performance
Run CHECKDB when the system usage is low.


Be sure that you are not performing other disk I/O operations, such as disk backups.


Place tempdb on a separate disk system or a fast disk subsystem.


Allow enough room for tempdb to expand on the drive. Use DBCC with ESTIMATE ONLY to estimate how much space will be needed for tempdb.


Avoid running CPU-intensive queries or batch jobs.


Reduce active transactions while a DBCC command is running.


Use the NO_INFOMSGS option to reduce processing and tempdb usage significantly.
Consider using DBCC CHECKDB with the PHYSICAL_ONLY option to check the physical structure of the page and record headers. This operation performs a quick check if hardware-induced errors are suspect.

©1988-2000 Microsoft Corporation. All Rights Reserved.

---