Problem
We have quite a lot of SQL Server jobs and as a production DBA I only want to get notifications when jobs fail. I get enough emails already and getting more emails will not help me work on the real problems. However, sometimes business owners want to get notifications for their jobs. As a DBA I don’t want to get these emails, but I do need an easy way to setup the notifications for the business owners. Since this needs to be done for several jobs I want a consistent and simple approach to do this.
Solution
As you may know you can setup a notification for a job whether it succeeds, fails or completes. Unfortunately you don’t have an option to send a different notification for each option. The other issue is that you need to setup operators for these notifications to be sent to, but if you have a lot of business users it may not make sense to create a bunch of different operators.
So in this tip I will walk through a customized approach that uses Database Mail and Tokens to keep this process as generic as possible without having to hardcode information about each job. This also still allows me to setup general failure notifications for all jobs to be sent to the DBAs.
In this tip we will be using Tokens that can be used from SQL Agent. If you are unaware of how tokens work, take a look at my previous article.. Also, tokens must be enabled for this process to work.
I have also created a stored procedure that can be used to dynamically pull the job name and send out an email using Database Mail.
Here is the stored procedure. I created this in the master database, but this can be created in any database.
/*Created: 1/30/2009
Author: Kun Lee
Description:
Version 0.5
Description:
Example1: this is for real job implementation.
exec usp_SendSuccessJobNotification @job_id = $(ESCAPE_SQUOTE(JOBID)),
@recipients = ‘youname@yourdomain.com’
Example2: This is more for debugging purpose.declare @jobid nvarchar(256)
declare @jobname varchar(256)
set @jobname = ‘Your Job name’
select @jobid = job_id
from msdb..sysjobs
where name = @jobname
exec usp_SendSuccessJobNotification ob_id = @jobid,
@recipients = ‘youname@yourdomain.com’
*/
CREATE proc [dbo].[usp_SendSuccessJobNotification]
@job_id uniqueidentifier,
@recipients varchar(200)
as
begin
DECLARE @job_name varchar(256)
DECLARE @subject varchar(256)
SELECT @job_name = name
FROM msdb..sysjobs
WHERE job_id = @job_id
SET @subject = ‘SQL Server Job System: [‘ + @job_name + ‘] succeeded on [‘ + @@servername + ‘]’
EXEC msdb.dbo.sp_send_dbmail
@recipients = @recipients,
@body = @subject,
@subject = @subject ;
END
Setup Job Notification
First create a job and job step the way you typically setup a job and make sure the On Success goes to the next job step as shown below.
Add another step, like step 2 below, that will be used to send the notification.
Open the additional step for the notification and put in the code as shown below. As you can see I am using the master database for this, so if you put the SP in another database you need to change the database where the SP exists.
Here is the code for the notification. The only thing you will need to do is change the list of recipients. You can include additional emails and separate them with a comma.
exec usp_SendSuccessJobNotification @job_id = $(ESCAPE_SQUOTE(JOBID)),
@recipients = ‘youname@yourdomain.com’
Process Without a Stored Procedure
If you don’t want to create a stored procedure you can just use the code below. This basically does the same thing, but you will need to copy all of this code into the job step. You would just need to update your email recipient list.
DECLARE @job_name varchar(256)ARE @subject varchar(256)
DECLARE @recipients varchar(256)
— Change your email address to email distribution group or person who needs to get
— Success Notification
SET @recipients = ‘Youremail@yourdomain.com’
SELECT @job_name = name
FROM msdb..sysjobs
WHERE job_id = $(ESCAPE_SQUOTE(JOBID))
SET @subject = ‘SQL Server Job System: [‘ + @job_name + ‘] succeeded on [‘ + @@servername + ‘]’
EXEC msdb.dbo.sp_send_dbmail
@recipients = @recipients,
@body = @subject,
@s@subject = @subject ;
Conclusion
There you have it.
If you are a DBA you can set the notification for failed jobs and not get any of the success emails in your inbox. This is a relatively easy process as long as you understand the concept of how tokens work.
Next Steps
- Now that you understand how tokens work, you can get creative with the process and the available data.
- The next article will be about setting up a job that runs frequently (like every 5 minutes) to check for failures and creates help desk tickets.
- You should also look into setting up monitoring for long running jobs and orphan jobs.
