A DBCC CHECKDB operation allow you to check the logical and physical integrity of all the objects in a specific database and by default returns the results of the check to the user console when run in SQL Server Management Studio.
When I was presenting on a topic covering Tips & Tricks of SQL Server Management Studio at IT/DEV Connections recently in Las Vegas I was approached after the session by an attendee asking how he could get the results delivered via email. At first I thought the solution to be quite simple and I told him as such. It was my thought you could simply create a SQL Server Agent Job that had two steps. The first to run the DBCC CHECKDB command and have the results of the command saved to a static text file that would be overwritten each time the scheduled job ran. The second step, to run whether the first step succeeded or failed, would run a sp_send_dbmail command that would send that text file to a specific email recipient or list of recipients. This was a solid answer until I put it to the test and realized I was likely 90-95% right with this architecture.
The Solution that Worked
The solution I offered up was solid with the exception of one minor issue: the output file. I'm a fan of using output files in SQL Server Agent Jobs because they can be more verbose than the logging the step output to a file which is another option. That's because there are length caps that exist by d
efault that limit (or more-precisely cut-off) the amount of information that is logged. Additionally, by default, step output is not included in the job creation process so it's easily missed. So back to that output file... what was the concern? Well it was simply to make sure that the same file is not used for both steps. What I tend to do, if I don't want a new output file each time the job runs, is to create the job with a static name where the first step is set to overwrite the existing file (if there is one) and then each subsequent step in that job run appends to that file. The end result is that I get a static file that has the output for all steps in order for that job execution cycle.
The issue with this type of solution though where you want to send the file that's generated is that you can't send the file when it's open. Even though the same user (in this case the SQL Server Agent service) is both the one holding the file open and also the "sender". Therefore the easy solution is to not have an output file for the second step (or have a different file for logging the output of the second step.
For sake of making this really easy to visualize lets shift to looking at this from the perspective of the SQL Server Management Studio GUI:
New SQL Server Agent Job
Create a new job that will eventually have two steps - one for running DBCC CHECKDB with the settings you want for that process and one for sending the email with the results:
Step One: The DBCC CHECKDB Command Step
Step Two: The sp_send_dbmail Command Step
Conclusion
With this simple configuration you can run individual DBCC CHECKDB commands against individual databases or multiple steps and have those results emailed to a list of recipients. This is not limited to just DBCC CHECKDB commands however. Any process where you want to alert staff to conditions or results from a query can be sent in such a fashion.
