Setting up a SQL Server DBCC CheckDB Script with Automatic Email
-
For original Blog Post and screenshots: http://joelradon.com/setting-up-a-sql-server-dbcc-checkdb-script-with-automatic-email/
Like many IT guys, I was thrown into the fire with a new responsibility a few years ago. When I first started learning SQL Server I felt like I was put on the beach in Normandy. Well, now I am the SQL guy where I work. Since most of what I do is not SQL related, I needed to find a way to automatically monitor our databases. I reached out to Robert L. Davis, you can visit his blog here, and he helped me with this process.
About the "CHECKDB like a Boss" Script
Robert 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 time. I went through the process of trial and error getting everything setup so you can learn from my experiences and mistakes.
There are two parts to this script. The first script creates a table for the CHECKDB results, and the other script actually runs the job. You only need to run the first script once. The other script we will setup as a job and schedule daily in this tutorial
Implementing the "CHECKDB like a Boss" Script
First we need to run the "dbo.CHECKDBResults.sql" script (You can download both Robert's Scripts here). Open up SQL Management Studio and select "New Query"
Now paste the "dbo.CHECKDBResults.sql" script and select Execute. (You only need to run this script once.)
Create Stored Procedure
Great! Now the table has been created so we are ready to start working on our "CHECKDB like a Boss" job. First we need to create a SQL stored procedure.
This will open an Stored Procedure template. You can go ahead and delete all the text in this template. We are now going to copy the script from :"dbo.dba_CHECKDBLikeABoss.sql"
After you paste the code, click Execute to create the Procedure.
Creating the Job
Now that the Stored Procedure has been created, we just need to create the job that will call this stored procedure.
Name the job, then select Steps.
Click New to create a Step.
Now we need to call up the Stored Procedure we created earlier. You can copy and paste this simple command. (Note: The default behavior of this script is to run CHECKDB on all databases so there is no need to create on a specific database. Just use the MASTER).
Now save your job and set your schedule to daily or whatever you'd like. There is no need to configure alerts or anything else here. As long as you have Database Mail enabled you will receive emails.
Testing your script
Now we need to run a quick test and make sure you are getting emails.
Once the job is finished you should see it complete successfully. Once it completes successfully you should receive an email.
Congratulations! You setup DBCC CHECKDB to run automatically and email you a status daily! See that wasn't too hard!
-
There seem to be 3 instances where links weren't put in correctly. 3/4 of the places you imply that there is a link actually have links.
