SQL Server Performance

DBCC CHECKDB - what are the exact commands run?

Discussion in 'SQL Server 2005 General DBA Questions' started by Trev256b, Nov 11, 2011.

  1. Trev256b Member

    Does anyone know the exact commands that are run under DBCC CHECKDB ? I know roughly what it does from reading BOL but want to know the fine detail.
  2. Luis Martin Moderator

    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.
  3. Trev256b Member

    Thanks Luis - I'm aware of this info - but thanks anyway. What I was looking for was the precise TSQL that the SQL Server DBMS runs.... is this documented anywhere? Or is this an internal process that Microsoft don't reveal?

Share This Page