CHECKDB

When you run DBCC CHECKDB in SQL Server, it performs a comprehensive set of checks to validate the logical and physical integrity of the database.

What is SQL Server DBCC CHECKDB

DBCC CHECKDB, from Microsoft MSDN Library, checks logical and physical integrity of all the objects in the specified database by performing the following operations:

· Runs DBCC CHECKALLOC on the database - Checks consistency of disk space allocation structures for a specified database.

·  Runs DBCC CHECKTABLE on every table and view in the database - Checks the integrity of all the pages and structures that make up the table or indexed view.

· Runs DBCC CHECKCATALOG on the database - Checks for catalog consistency within the database.

·  Validates the contents of every indexed view in the database.

·  Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM.

·  Validates the Service Broker data in the database

Overall, DBCC CHECKDB is a crucial maintenance command that helps ensure the integrity and reliability of a SQL Server database by checking both logical and physical aspects of its data and structures.

Here's a complete picture of what happens behind the scenes when DBCC CHECKDB runs:

1.      Initialization:

·   DBCC CHECKDB initializes by setting up necessary data structures and resources for the integrity checks. It acquires the necessary locks to ensure consistency during the process.

2.      Database Snapshot Creation (Optional):

·   If DBCC CHECKDB is executed with the WITH TABLERESULTS or WITH SNAPSHOT option, it creates a database snapshot to perform the checks. This snapshot ensures a consistent view of the database while CHECKDB is running, preventing interference from concurrent transactions.

3.      Allocation Integrity Checks:

·  CHECKDB begins by checking the allocation structures of the database, including pages, extents, and allocation units. It verifies the integrity of these structures to ensure they are correctly linked and organized, detecting any physical corruption or inconsistencies.

DBCC CHECKDB also checks the physical integrity of the database at the storage level. This involves:

·      Verifying the integrity of data pages, extents, and allocation units.

·      Detecting and repairing physical corruption, such as torn pages or checksum errors.

·      Ensuring that data pages are correctly linked and organized within the database files.

4.      Structural Checks:

·      After completing the allocation integrity checks, CHECKDB verifies the structural integrity of database objects such as tables, indexes, system catalog views, and metadata. It examines the object definitions, relationships, and dependencies to ensure they are consistent and correctly maintained.

5.      Logical Checks:

·     DBCC CHECKDB performs logical checks on data pages within the database. It validates data values, checks for orphaned records, verifies referential integrity, and ensures data consistency across related objects. This phase detects logical inconsistencies or corruption that may not be evident from allocation or structural checks alone.

DBCC CHECKDB examines the logical consistency of data within the database. This includes checking for:

·      Orphaned records: Records that do not have corresponding parent records in related tables.

·      Referential integrity: Ensuring that foreign key constraints are not violated.

·      Data type consistency: Verifying that data types are consistent across columns and tables.

·      Indexes and views: Checking the structure and consistency of indexes and views.

·      System tables and metadata: Verifying the integrity of system tables and metadata.

6.      Parallel Processing:

·    CHECKDB may utilize parallel processing to improve performance, especially on multi-core systems. It divides the workload into multiple threads responsible for checking different parts of the database concurrently, reducing the overall execution time.

7.      Reporting:

·    As CHECKDB progresses through the checks, it generates a detailed report summarizing the results. The report includes information about any corruption or inconsistencies found, along with recommendations for repair options if applicable.

8.      Repair Options (If Needed):

·      Depending on the severity of corruption detected, DBCC CHECKDB may offer repair options (REPAIR_ALLOW_DATA_LOSS, REPAIR_FAST, or REPAIR_REBUILD) to attempt automatic correction of certain issues. However, these options should be used cautiously as they can cause data loss or data integrity issues.

9.      Completion and Cleanup:

·   Once DBCC CHECKDB completes all checks and generates the report, it releases resources used during the execution and performs any necessary cleanup tasks.

Overall, DBCC CHECKDB is a critical maintenance command that helps ensure the integrity and reliability of a SQL Server database by performing comprehensive checks and validations. It plays a vital role in detecting and resolving corruption, ensuring data consistency, and maintaining database health.

 

SQL Server DBCC CHECKDB Options

There are a few options to use with DBCC CHECKDB and I'll go over a few of the more popular ones here:

• NOINDEX - Specifies that intensive checks of nonclustered indexes for user tables should not be performed. This decreases the overall execution time. NOINDEX does not affect system tables because integrity checks are always performed on system table indexes.
• NO_INFOMSGS - Suppresses all information messages.
• PHYSICAL_ONLY - Limits the checking to the integrity of the physical structure of the page and record headers and the allocation consistency of the database. This check is designed to provide a small overhead check of the physical consistency of the database, but it can also detect torn pages, checksum failures, and common hardware failures that can compromise a user's data.
• TABLOCK - Causes DBCC CHECKDB to obtain locks instead of using an internal database snapshot. This includes a short-term exclusive (X) lock on the database. 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.
• DATA_PURITY - Causes DBCC CHECKDB to check the database for column values that are not valid or out-of-range. For example, DBCC CHECKDB detects columns with date and time values that are larger than or less than the acceptable range for the datetime data type; or decimal or approximate-numeric data type columns with scale or precision values that are not valid.

We'll go over some of the REPAIR options in a different section below.