solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips






Learn more about SQL Server tools








Learn more about SQL Server tools


   Got a SQL tip?
            We want to know!

Customized SQL Agent Job Notifications

MSSQLTips author Kun Lee By:   |   Read Comments (1)   |   Related Tips: More > SQL Server Agent
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.

SQL Jobs

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.

you can make the SQL script generic

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.


Last Update: 2/14/2013


About the author
MSSQLTips author Kun Lee
Kun Lee is a database administrator and his areas of interest are database administration, architecture, data modeling and development.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Thursday, February 14, 2013 - 11:26:35 PM - Dev Patel Read The Tip

Excellent tip. Very useful for many jobs for different business owner. 

Looking forward to read your article on jobs running every 5 mins....



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 

Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.