Setting up an e-mail alert for DBCC CHECKDB errors
Some months ago I posted a script on a SQLServerCentral forum to help a member automating the execution of DBCC CHECKDB and send and e-mail alert in case a consistency error is found.
The original thread can be found here.
I noticed that many people are actually using that script and I also got some useful feedback on the code itself, so I decided to write this post to make an enhanced version available to everyone.
Your primary responsibility as a DBA is to safeguard your data with backups. I mean intact backups! Keep in mind that when you back up a corrupt database, you will also restore a corrupt database.
A task that checks the database integrity should be part of your backup strategy and you should be notified immediately when corruption is found.
Unfortunately, the built-in consistency check Maintenance Task does not provide an alerting feature and you have to code it yourself.
SQL Server 2000 and above accept the “WITH TABLERESULTS” option for most DBCC commands to output the messages as a result set. Those results can be saved to a table and processed to identify messages generated by corrupt data and raise an alert.
If you don’t know how to discover the resultset definition of DBCC CHECKDB WITH TABLERESULTS, I suggest that you take a look at this post.
Here is the complete code of the stored procedure I am using on my production databases:
Once the stored procedure is ready, you can run it against the desired databases:
EXEC [maint].[dba_runCHECKDB] @dbName = 'model', @PHYSICAL_ONLY = 0, @allmessages = 0
Setting up an e-mail alert
In order to receive an e-mail alert, you can use a SQL Agent job and schedule this script to run every night, or whenever you find appropriate.
EXEC [maint].[dba_runCHECKDB] @dbName = NULL, @PHYSICAL_ONLY = 0, @allmessages = 0, @dbmail_profile = 'DBA_profile', @dbmail_recipient = 'firstname.lastname@example.org'
The e-mail message generated by the stored procedure contains the summary outcome and a detailed log, attached as a text file:
Logging to a table
If needed, you can save the output of this procedure to a history table that logs the outcome of DBCC CHECKDB in time:
-- Run the stored procedure with @log_to_table = 1 EXEC TOOLS.maint.dba_runCHECKDB @dbName = NULL, @PHYSICAL_ONLY = 0, @allMessages = 0, @log_to_table = 1 -- Query the latest results SELECT * FROM ( SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY DBId ORDER BY RunDate DESC) FROM DBCC_CHECKDB_HISTORY WHERE Outcome IS NOT NULL ) AS dbcc_history WHERE RN = 1
When invoked with the @log_to_table parameter for the first time, the procedure creates a log table that will be used to store the results. Subsequent executions will append to the table.
The web is full of blogs, articles and forums on how to automate DBCC CHECKDB. If your data has any value to you, CHECKDB must be part of your maintenance strategy.
Run! Check the last time you performed a successful CHECKDB on your databases NOW! Was it last year? You may be in big trouble.