Some recommendations for easy access to DBCC CHECKDB

In Microsoft®sql server™2000, you can run DBCC CHECKDB when a user uses a database, because the types of locks that are controlled on a table by the DBCC CHECKDB are changed when they check each database table.

In SQL Server 7.0 and earlier versions, DBCC CHECKDB, which runs DBCC CHECKTABLE and checkalloc on each table of the database in turn, often controls shared locks (S) on the table, blocking all data modification language (DML) statements.

In SQL Server 2000, DBCC CHECKDB controls schema locks on the table when the table is checked to prevent metadata changes, allowing the use of DML statements except for any data definition language (DDL) statements on the table being checked. This change provides greater flexibility in deciding when to run DBCC CHECKDB because DBCC CHECKDB does not completely deny users the use of the system.

DBCC CHECKDB are CPU-and disk-intensive operations. Each data page that needs to be checked must first be read from disk into memory. In addition, the DBCC CHECKDB uses the tempdb sort.

If the transaction is executed dynamically at the DBCC CHECKDB runtime, the transaction log continues to grow because the DBCC command blocks log truncation before the log is read.

It is recommended that you run DBCC CHECKDB when the server load is low. If DBCC CHECKDB is run during peak load times, transaction throughput performance and DBCC CHECKDB completion time performance are affected.

Some recommendations for good DBCC performance

Run CHECKDB when system usage is low.

Make sure that other disk I/O operations, such as backup to disk, are not performed concurrently.

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

Allows tempdb to have enough extended space on the drive. Use DBCC with estimate only to estimate how much space will be needed for tempdb.

Avoid running a large CPU-intensive query or batch job.

Reduces active transactions while the DBCC command is running.

Use the NO_INFOMSGS option to significantly reduce the use of processing and tempdb.

Consider using the DBCC CHECKDB with the physical_only option to check the physical structure of the page and record header. This action performs a quick check when the error caused by the hardware is questioned.