Monday, December 14, 2009

DBCC CHECKDB requires space in tempdb.

It Checks the allocation and structural integrity of all the objects in the specified database.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.

No comments: