DBCC CHECKDB Options Explained

In SQL Server, the DBCC CHECKDB command is a critical tool for ensuring the integrity of your databases. It performs checks on the logical and physical integrity of all the objects in the specified database. Using DBCC CHECKDB with different options can tailor the scope and performance impact of these integrity checks.

`DBCC CHECKDB` with No Options

Running DBCC CHECKDB without any options performs a comprehensive check, including:

Integrity of database pages and structures.
Consistency of disk space allocation.
Integrity of index structures.
Validation of data constraints.
Checks on indexed view content.
Link-level consistency for FILESTREAM data.
Data purity checks for column value integrity (for SQL Server 2005 and later).

This comprehensive check is resource-intensive and suitable for thorough integrity verification.

`DBCC CHECKDB` with `PHYSICAL_ONLY`

The PHYSICAL_ONLY option limits the check to the physical integrity of the pages and records, including:

Physical disk space allocation consistency.
Minimal checks for physical errors.

This option is faster, suitable for frequent use on large databases to quickly detect physical corruption.

`DBCC CHECKDB` with `DATA_PURITY`

Data purity checks validate column values against their data types. These checks are included by default in full checks for databases created in SQL Server 2005 and later. For databases upgraded from earlier versions, DATA_PURITY needs to be explicitly specified to initiate these checks.

Summary

No options: Most comprehensive, checking both logical and physical integrity along with data purity.
PHYSICAL_ONLY: Quick, physical integrity checks for detecting hardware-related corruption.
DATA_PURITY: Ensures data type compliance for all column values, included by default since SQL Server 2005.

For maintaining and monitoring SQL Server databases, Stedman Solutions offers expertise in identifying and resolving performance issues and ensuring database integrity. Additionally, the Database Health Monitor tool supports maintaining database health and performance. Visit Stedman’s SQL School for more details and training opportunities.