Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

There are plenty of questions on DBA.SE regarding DBCC CHECKDB and how to resolve problems when errors are returned. My specific question is on actually getting notified that DBCC CHECKDB returned errors. Most all DBAs know that you can automate the command and should run it often.

I came across this article by Cindy Gross, which has some very good notes. In it she mentions use of SQL Server Agent that if it finds errors from the execution of the CHECKDB command it will fail that step (or job depending on configuration). She points to Paul Randal's blog post on the topic here.

Now I am curious if anyone knows that the Check Database Integrity Task in a maintenance plan would do the same thing? MSDN does not mention that it will and I have not truthfully been an environment where it has come across a corruption issue; so can't say that it does. This would be versus simply setting up a SQL Agent Job with multiple steps that runs the specific command against each database, as Cindy suggested.

Thoughts? Obviously proof is in the pudding so providing more than just a guess would be helpful...

share|improve this question
add comment

3 Answers

The Check Database Integrity Task provided in the maintenance plan issue DBCC CHECKDB WITH NO_INFOMSGS on the database selected. You can view its command by clicking the view-SQL in the task setup. If you doubt the generated SQL command, you can use SQL profiler to see its SQL command. If corruption was found, the agent job with this maintenance task will generate error and fail (with proper job step setup).

enter image description here

One thing to point out, running DBCC CHECKDB is equal as performing DBCC CHECKALLOC, DBCC CHECKTABLE, DBCC CHECKCATALOG and other validation. If you are running DBCC CHECKDB, you do not have to run them separately. Running them separately usually is to perform specific integrity check or need to spread out the integrity check to smaller task due to limited time to perform entire DBCC CHECKDB. More information can be found here on MSDN.

share|improve this answer
    
If you have the task setup to run against multiple databases and say the second in 3 databases it checks finds errors, it would never run against the 3rd database because the job would fail? –  Shawn Melton Apr 8 '13 at 1:37
    
The integrity check job will run through all databases previously setup for integrity check even if it encounters corruption on other database (e.g. the 2nd database). If you have 2 databases that are corrupted, the maintenance plan history will show you 2 errors with error output of each database accordingly. –  Travis Gan Apr 8 '13 at 2:21
add comment
up vote 2 down vote accepted

I setup a SQL Server 2008 R2 instance and have the following databases:

I setup a Maintenance Plan with the Check Database Integrity Task in it. enter image description here

After running the plan it did fail: enter image description here

After reviewing the log it showed that it will continue checking additional databases after hitting one that failed from corruption being found: enter image description here

share|improve this answer
add comment

To get notified about DBCC CHECKDB errors I parse the error log with Powershell every night and it emails us when there is an error in the SQL error log and specifically if there are DBCC errors

It's the easiest way for us to monitor 700+ databases

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.