CheckDB Advisor

The CheckDB advisor is a component that presents a visual interface over the SQL Server DBCC CheckDB commands.

Just a quick way to access DBCC CheckDB when you are using the Last Known Good DBCC CheckDB report.

In SQL Server, the DBCC CHECKDB command is a critical tool for ensuring the integrity of your databases. It checks 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. Let’s break down the differences between using DBCC CHECKDB with no options, compared to using it with the PHYSICAL_ONLY and DATA_PURITY options.

DBCC CHECKDB with No Options

When you run DBCC CHECKDB without any options, SQL Server performs a comprehensive check. This includes:

• Ensuring the integrity of all the database pages and structures.
• Checking the consistency of disk space allocation.
• Validating the integrity of index structures.
• Ensuring that every row in a table has a corresponding index row, and vice versa (and various other integrity checks on indexes).
• Checking constraints to validate that all data complies.
• Validating the content of every indexed view in the database.
• Checking link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM.
• Optionally, as of SQL Server 2005 and later, DBCC CHECKDB also checks for column value integrity to ensure that all values in a column comply with the data type of the column, known as data purity checks. This is automatically included unless explicitly turned off for databases that were upgraded from earlier versions.

This comprehensive check ensures that the database is in a consistent and reliable state, but it can be resource-intensive and time-consuming, especially for large databases.

DBCC CHECKDB with PHYSICAL_ONLY

The PHYSICAL_ONLY option limits the check to the physical integrity of the pages and records. Specifically, it:

• Checks the consistency of the physical disk space allocation for the database.
• Includes minimal checks for physical errors in the database, but it does not check the integrity of all the data and index relationships.
• Is much faster than the full DBCC CHECKDB, making it suitable for frequent use on large databases where time windows for maintenance are limited.

This option is often used as a quicker way to detect torn pages, checksum failures, and other hardware-related corruption issues without the overhead of a full logical check.

DBCC CHECKDB with DATA_PURITY

DATA_PURITY checks ensure that all column values are valid according to their column data type. Before SQL Server 2005, DBCC CHECKDB did not include data purity checks, so databases upgraded from versions prior to SQL Server 2005 can have rows with values that are not valid for their data type (e.g., a date column containing an impossible date). Since SQL Server 2005, DBCC CHECKDB includes DATA_PURITY checks by default for databases created in SQL Server 2005 and later. For databases upgraded from earlier versions, you must explicitly include DATA_PURITY the first time you run DBCC CHECKDB to initiate these checks.

Summary

• DBCC CHECKDB with no options performs a comprehensive check, including both logical and physical integrity checks, data purity (for SQL Server 2005 and later databases), and more. It’s the most thorough option but also the most resource-intensive.
• DBCC CHECKDB with PHYSICAL_ONLY focuses on the physical integrity of the database, making it a quicker option for detecting physical corruption.
• DBCC CHECKDB with DATA_PURITY checks are included by default in full checks for databases created in SQL Server 2005 and later. For older databases, specifying DATA_PURITY explicitly enables these checks, ensuring all data complies with its data type.

For maintaining and monitoring SQL Server databases, Stedman Solutions offers expertise in identifying and resolving performance issues, ensuring database integrity, and optimizing SQL Server environments. Additionally, the Database Health Monitor tool can be invaluable in keeping an eye on the health and performance of your SQL Servers, further supporting your efforts to maintain database integrity.

Need Help

Stedman Solutions, the provider of the Database Health Monitor Application offers consulting solutions, and can help with any CheckDB or database integrity issues, or any other database administration issues you may have.

Enroll Today!

---

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!