RYO Maintenance Plan – Integrity Checks

2012/03/19

Happy Monday! Everyone fully recovered from St. Patrick’s Day? Hopefully the weather where you are was as perfect as it was here in Cleveland.

Today’s piece of RYO Maintenance Plan deals with database integrity checks; a critical component second only to backups in my book. The procedure I’ve written is really just a wrapper for the DBCC CHECKDB command. But before I jump into the code, let’s take a moment and talk about what exactly DBCC CHECKDB does. In a nutshell, CHECKDB checks both the logical and physical integrity of all of the objects in your database. It accomplishes this by stepping through a series of lower-level checks.

DBCC CHECKTABLE checks the logical and physical integrity of individual tables. It validates that every row in a table has a corresponding record in any non-clustered index on the table, and, inversely, that all index records have a row in the table. Index sort order is verified, partitions are checked to ensure they contain the correct rows, data is examined to ensure it is within the acceptable range for its data type. On a physical level, data pages are checked to verify they’re linked properly.

DBCC CHECKCATALOG examines the data inside the system catalog tables, verifying the consistency in and between the system tables.

Check Service Broker objects and validate the relationships between them. A service has to be assigned to a valid queue and contract, etc.

Validate the relational engine metadata in the system catalog.

Verify that all indexed views contain the correct data.

What you may not know is that SQL Server requires a transactionally consistent view of the database in order to reliably perform these checks. To achieve this, SQL Server actually creates a hidden snapshot of your database and runs the checks against that snapshot. This snapshot is created inline with your database datafile(s). As you’ll recall, snapshots use sparse files that start out small but grow as data is changed in your source database. If you’re low on disk space and are running CHECKDB on a busy system, you run the risk of possibly filling up your data drive. If you’re concerned about this, you can always manually create a snapshot, placing the files in a better location, and run CHECKDB against that snapshot. It will work just as well.

Ok, so now we know a little bit more about what DBCC CHECKDB is doing, let’s take a look at the procedure I’m using. It’s so darn simple I won’t insult your intelligence by stepping through it. The only options I included were a parameter to specify the target database (just like the previous procedures) and a flag to specify whether or not to display informational messages. The default is ‘N’, to suppress these messages (my personal preference).