If you missed any of the earlier posts in my DR series, you can check them out here:
- 31 Days of disaster Recovery
- Does DBCC Automatically Use Existing Snapshot?
- Protection From Restoring a Backup of a Contained Database
- Determining Files to Restore Database
- Back That Thang Up
- Dealing With Corruption in a Nonclustered Index
- Dealing With Corruption in Allocation Pages
- Writing SLAs for Disaster Recover
- Resolutions for All DBAs
- Use All the Checksums
- Monitoring for Corruption Errors
- Converting LSN Formats
- Extreme Disaster Recovery Training
- Standard Backup Scripts
- Fixing a Corrupt Tempdb
- Running DBCC CheckTable in Parallel Jobs
- Disaster Recovery Gems From Around The Net
- 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)
Something for the Weekend - SQL Server Links 25/01/13
[…] Day 18 of 31 Days of Disaster Recovery: How to CHECKDB Like a Boss – Continuing his excellent series it’s Robert L. Davis(Blog|Twitter). Here he shares a script for us to put to work. […]
Day 22 of 31 Days of Disaster Recovery: Which DBCC CHECK Commands Update Last Known Good DBCC | SQLSoldier
[…] How to CHECKDB Like a Boss […]
Day 24 of 31 Days of Disaster Recovery: Handling Corruption in a Clustered Index | SQLSoldier
[…] How to CHECKDB Like a Boss […]
Day 27 of 31 Days of Disaster Recovery: Restoring Part of a Database | SQLSoldier
[…] How to CHECKDB Like a Boss […]
Thor
Hi,
Thanks for a great set of scripts. I have a question though: when I execute dba_CHECKDBLikeABoss ande let it default to NUll (so I can have it iterate through all the databases, I see an error: Msg 50000, Level 16, State 1, Procedure dba_CHECKDBLikeABoss, Line 93
DBCC CHECKDB failed for the following databases: (null). Check dbo.CHECKDBResults for more information. The CHECKDBResults table is empty.
Am I doing something wrong? Thanks.
SQLSoldier
Thanks for catching and reporting this! I tested it with a wide variety of errors, but the one thing I forgot to test it with is what if there are no failures. It’s raising the error at the end even if there are no failures detected.
I have uploaded a new version of the procedure that returns a successful message if no errors are found.
Thanks again for your help!
Setting up a SQL Server DBCC CheckDB Script with Automatic Email – Joel Radon
[…] introduced me to this awesome script he wrote for this exact purpose (You can see his original post here). The script is great, but can be a little confusing for beginners to set it up for the first […]