When setting up new servers, every DBA sets up a standard set of jobs to maintain the server. This includes tasks such as data backups (full and differential), log backups for disaster recovery, index rebuilds to maintain application efficiency, and so on. Normally if there is one server, the DBA can use SQL Server Management Studio to view job history for the latest execution:
However, this becomes time-consuming if you start scaling these servers to more than one and DBAs have better things to do with their time. We can start automating the process by replacing the steps with a query such as
SELECT * FROM msdb..sysjobhistory WHERE step_id = 0
This is still a time-consuming process; as the number of jobs increase on a single server, the time to review them will increase over time.
The final step in our monitoring will use T-SQL to format a message and e-mail it to a notification group. There are several pre-requisites you should be aware of which are beyond the scope of this article:
- a profile and e-mail account should be configured
- e-mail should be configured to execute within SQL Server
Open the link below, and copy and paste the following script to create the job notification.
SQL Server Job Monitor E-Mail Open SQL Code
Create a new job
Add the job description and name
Add a new step, and paste the code
Close the step detail. On the job, select the Schedules tab. if you do not already have a daily schedule, you can create one here, and it may look like this:
You can wait for the next schedule, or execute the job immediately to test it. If you have never executed a job before, you will not receive an e-mail, but if you have, you should receive an e-mail that looks similar to the following:
