DBCC CHECKDB

  • Article

DBCC CHECKDB checks the logical and physical integrity of all objects in the specified database by performing the following:

  • Runs DBCC CHECKALLOC on the database
  • Runs DBCC CHECKTABLE on every table and view in the database
  • Runs DBCC CHECKCATALOG on the database
  • Validates the contents of every indexed view in the database
  • Validates the service broker data in the database

DBCC CHECKALLOC:

  • Validates the allocation information maintained in the GAM, SGAM and IAM pages
  • Performs cross check to verify that every extent that the GAM or SGAM indicates has been allocated really has been allocated, and that any extents not allocated are indicated in the GAM and SGAM as not allocated
  • Verifies the IAM chain for each allocation unit, including the consistency on the links between the IAM pages in the chain
  • Verifies that all extents marked as allocated to the allocation unit really are allocated

DBCC CHECKTABLE:

  • Performs a comprehensive set of checks on the structure of a table, and by default these are logical and physical
  • With physical-only option, you can exclude the logical checks and only validate the physical structure of the page and record headers
  • PHYSICAL OPTION is a lightweight check of the physical consistency of the table and common hardware failures that can compromise data
  • Indexed views are verified by regenerating the view's rowset from the underlying select statement definition and comparing the results with the data stored in the indexed view
  • SQL performs 2 left anti-semi joins between the 2 rowsets

DBCC CHECKCATALOG:

  • Performs more than 50 cross-checks between various metadata tables
  • cannot fix errors it finds by running the DBCC operation with any of the REPAIR options

SERVICE BROKER Data is Verified:

  • Only way to check the service broker data as there is no specific DBCC command to perform the checks
  • DBCC CHECKFILEGROUP can also be considered to be a subset of CHECKDB because it performs DBCC CHECKTABLE on all tables and views in a specified filegroup

DBCC will run faster on a SQL 2005 database upgraded from 2000 but with no 2005 features or indexed views. On a new 2005 database, some of the logical checks added to compliment new features are necessarily complex and add to the runtime when invoked.

All the DBCC validation commands use database snapshots to keep validation operations from interfering with on-going database operations and to allow the validation operation to see a quiescent, consistent view of the the data. A snapshot is created at the beginning of the CHECK command, and no locks are acquired on any of the objects being checked. The actual check operation is performed against the snapshot. Unlike regular snapshots, the "snapshot file" that DBCC CHECKDB creates cannot be configured and is invisible to the end user. It always uses apace on the same volume as the database being checked. This capability is only available when your data directory is on an NTFS partition.

  • You can avoid creating a snapshot (to save disk space for example) by using the WITH TABLOCK option with the DBCC command
  • If you are using one of the repair options, a snapshot is not created as the database is in single-user mode, so no other transactions can be altering data
  • Without TABLOCK, DBCC is considered to be an online operation
  • DBCC validation checks require a significant amount of space as SQL Server needs to temporarily store data about pages and structures that have been observed during the check operation against pages and structures that are observed later during the DBCC scan
  • To determine the space tempdb needs in advance, run CHECKDB with the ESTIMATE ONLY option:
    • SET NOCOUNT ON;
      DBCC CHECKDB ('AdventureWorks') WITH ESTIMATEONLY;
    • This is computed as a worst-case estimate and assumes there will be no room in memory for any of the sort operations required

VALIDATION CHECKS

SQL Server 2005 includes a set of logical validity checks to verify that data is appropriate for the columns data type. These can be expensive and affect server performance, so you can disable these, along with other non-core logical validations by using the PHYSICAL-ONLY option. All new databases in SQL Server 2005 have the DATA_PURITY logical validations enabled by default. For upgraded databases, you must run the DBCC CHECKDB with the DATA_PURITY option once, preferably immediately after the upgrade

DBCC CHECKDB ('dbname') WITH DATA_PURITY

After the purity check completes without any errors, performing the logical validations is the default behavior in all future executions of DBCC CHECKDB and there is no way to change this default. You can override the default by using the PHYSICAL_ONLY option. This skips the data purity check but also skips any checks that have to analyze the contents of individual rows of data and limits the checks that DBCC performs.

DBCC REPAIR OPTIONS 

There are three DBCC repair options:

  • REPAIR_ALLOW_DATA_LOSS
  • REPAIR_REBUILD
  • REPAIR_FAST (legacy backward compatibility only)

Almost all errors can be repaired except:

  • DBCC CHECKCATALOG errors
  • Data purity errors found through DBCC CHECKTABLE

 When you run DBCC CHECKTABLE with one of the repair options:

  • SQL runs DBCC CHECKALLOC and repairs what it can
  • Then runs DBCC CHECKTABLE on all tables and makes the appropriate repairs on all the tables
  • List of repairs are ranked to avoid duplication of effort (i.e. rebuilding an index, then removing a page from a table would duplicate effort)

REPAIR_ALLOW_DATA_LOSS option:

  • SQL Server tries to repair almost ALL detected errors.
  • For almost any severe error, some data will be lost when the repair is run.
  • Rows may be deleted if they are found to be inconsistent, such as when a computed column value is incorrect.
  • Whole pages can be deleted if checksum errors are discovered
  • During the repair, no attempt is made to maintain any constraints on the tables, or between tables
  • Some errors SQL Server won't even try to repair, particularly if the GAM or SGAM pages themselves are corrupt or unreadable.

REPAIR_REBUILD option:

  • Minor, relatively fast repair actions such as repairing extra keys in a non-clustered index
  • Time consuming repairs, such as rebuilding indexes
  • can be performed without risk of data loss
  • After successful completion, database is physically consistent and online but may not be in a logically consistent state in terms of constraints and business rules

Use repair options only as a last resort, restore the database from backup instead. If you are going to run repair_allow_data_loss, backup your database first. You can run the repair options for DBCC inside a user-defined transaction, which means you can perform a ROLLBACK to undo the repairs. Exception is when you are running the repair options on a database in EMERGENCY mode. If a repair in emergency mode fails, there are no other options except to restore the database from a backup.

Each individual repair in a DBCC operation runs in it's own system transaction, which means that if a repair is not possible, it will not affect any other repairs.

Create a snapshot before the repair is initiated, start a transaction, then run the REPAIR option. Before committing or rolling back, you can compare the repaired database with the original snapshot. If you are not satisfied with the changes made, rollback the repair operation.

PROGRESS REPORTING:

  • Use sys.dm_exec_requests
  • Command column indicates current DBCC command phase
  • Percent_completed represents %age completion of the DBCC command phase
  • Estimated_completion_time represents the time in ms of how long it will take to complete

DBCC BEST PRACTICES

Use CHECKDB with CHECKSUM option and a sound backup strategy to protect the integrity of the database from hardware failures

Perform DBCC CHECKDB WITH DATA_PURITY after upgrading a database to SQL Server 2005 to check for invalid data values

Make sure you have enough disk space available to accommodate the database snapshot

Make sure you have space available ion tempdb to allow the DBCC command to run. Use the ESTIMATEONLY command to find out how much space is required in tempdb

Use REPAIR_ALLOW_DATA_LOSS only as a last resort