Day 18 of 31 Days of Disaster Recovery: How to CHECKDB Like a Boss

Day 18 of my 31 Days of Disaster Recovery series is drawing to a close. It’s 11:22 PM here, and I’ve been working feverishly to finish today’s post before the calendar flips over to tomorrow. This started out as sharing a simple script I use for running DBCC CHECKDB against all databases on a server, and like I tend to do, I thought of lots of things I wanted to add to it. I spent a several hours customizing my “this will be a quick blog post because the script is already written” script.

If you missed any of the earlier posts in my DR series, you can check them out here:

31 Days of disaster Recovery
1. Does DBCC Automatically Use Existing Snapshot?
2. Protection From Restoring a Backup of a Contained Database
3. Determining Files to Restore Database
4. Back That Thang Up
5. Dealing With Corruption in a Nonclustered Index
6. Dealing With Corruption in Allocation Pages
7. Writing SLAs for Disaster Recover
8. Resolutions for All DBAs
9. Use All the Checksums
10. Monitoring for Corruption Errors
11. Converting LSN Formats
12. Extreme Disaster Recovery Training
13. Standard Backup Scripts
14. Fixing a Corrupt Tempdb
15. Running DBCC CheckTable in Parallel Jobs
16. Disaster Recovery Gems From Around The Net
17. When are Checksums Written to a Page

CHECKDB Like a Boss

As I said, this script started out as a simple script to run DBCC CHECKDB against every database on a server. As I was making it an “official” script, meaning one I feel is suitably evolved for sharing, I came up with several ideas for improving it. The scripts that are attached to this blog posts consists of the T-SQL to create a table to track results of the DBCC CHECKDB run to refer back to later when you see that an error occurred and a stored procedure that you can schedule via a SQL job to run regularly.

I added a couple of parameters to the procedure as follows:

• @DBName: Allows you to specify a specific database to run it on. The default is NULL which will run it against all online databases.
• @UseSnapshotIfExists: If a database snapshot already exists, this allows you to specify whether DBCC CHECKDB should be run against the snapshot instead of the live database. If multiple snapshots exist, it will run against the most recently created snapshot. A value of 1 means it will run against the existing snapshot, and 0 (default) will ignore database snapshots.

Example usage:

Exec dbo.dba_CHECKDBLikeABoss @UseSnapshotIfExists = 1;

The procedure captures the error output into the table and will raise a Severity 16 error at the end if any DBCC CHECKDB checks failed telling you which databases failed and directing you to check the logging table. The error is also raised to the Windows event log in case you use monitoring software that scans the event log for errors. This process provides several ways to catch the failure (you know, just in case), the job failing, the error raised in SQL, and the error raised in the Windows event log.

I considering writing a script to purge old data from the logging table, but if your server needs regular purging of this table, you’ve got major problems. You should be fine to simply truncate this table any time you’ve been error free for a while and know you no longer need the historical data. This table should not need frequent purging.

Download the scripts as a zip file: CHECKDBLikeABoss.zip (2 KB)