Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Setting Up Alerts for All SQL Server Agent Jobs


By:   |   Last Updated: 2006-10-26   |   Comments (4)   |   Related Tips: More > SQL Server Agent

Problem
One of the things you can do with SQL Server 2000 is setup Operators to be notified when there is a job failure or an alert has been triggered that should notify your DBA team.  The problem with setting up notifications for jobs is that you need to edit each and every job in order to turn on notifications.  If you only have a few jobs this is not that much work, but if you have several jobs and have several servers making this change may take some time.  In addition, if you are using Maintenance Plans there is not a way to automatically setup notifications for the jobs, so you would have to do this manually as well. Luckily there is a stored procedure that can help make this task easier.

Solution
As with everything you can do through Enterprise Manager there is also a T-SQL equivalent command or commands that can streamline the effort it takes to make mass updates. One of these stored procedures is "sp_update_job".  This stored procedure is found in the msdb database and allows you to make changes to your job without having to use the GUI.

So lets take a look at an example of how you can do this across the board for all jobs much quicker then using Enterprise Manager.  Before we begin you need to already have SQLMail setup in order to use this as well as have SQL Agent setup to use the mail account that you are using.  There are other tips on the internet on how to do this, so we will not go through these steps.

Let's say we have an operator already setup called "SQLalerts" and we want to update all of our jobs to use this operator in case of a job failure.  Again we could open each of our jobs and make the change manually, such as the following screen shows:

Like I mentioned earlier, this is easy to do for a few jobs, but if you have many jobs and\or servers it can be very time consuming.

Here is a sample T-SQL script that will create the commands for you.  This script actually outputs the commands that can then be copied and pasted into QA and executed to make the updates.  The script could also be changed to automatically issue the commands, but with this version you have the ability to look at the commands first before you execute them.

use msdb
GO

DECLARE @operator varchar(50)
SET @operator = 'SQLalerts'

SELECT 'EXEC msdb.dbo.sp_update_job @job_ID = ''' + convert(varchar(50),job_id) + ''' ,@notify_level_email = 2, @notify_email_operator_name = ''' + @operator + '''' FROM sysjobs

When this gets run the following output is created:

EXEC msdb.dbo.sp_update_job @job_ID = '589D2B60-EDBD-45B5-BDE6-4DD974D20D25' ,@notify_level_email = 2, @notify_email_operator_name = 'SQLalerts'

EXEC msdb.dbo.sp_update_job @job_ID = '6BE4306C-CC37-4D38-BC27-1B099601EF6A' ,@notify_level_email = 2, @notify_email_operator_name = 'SQLalerts'

EXEC msdb.dbo.sp_update_job @job_ID = 'F7569D9A-641E-4130-90F4-535F0B11FC1E' ,@notify_level_email = 2, @notify_email_operator_name = 'SQLalerts'

EXEC msdb.dbo.sp_update_job @job_ID = 'CD012AF2-BC96-4D9E-A03E-6ABB2F6048AF' ,@notify_level_email = 2, @notify_email_operator_name = 'SQLalerts'

EXEC msdb.dbo.sp_update_job @job_ID = '451C94B4-8BA3-48AA-BB66-D184F0C25556' ,@notify_level_email = 2, @notify_email_operator_name = 'SQLalerts'

EXEC msdb.dbo.sp_update_job @job_ID = '7EA95731-1E19-40F6-A5E3-325647DACDE9' ,@notify_level_email = 2, @notify_email_operator_name = 'SQLalerts'
 

The results from the above can be copied and pasted into Query Analyzer to make the actual updates to your jobs. After the commands are executed all of my jobs now have "SQLalerts" as the operator to get notified when anyone of these jobs fail.

You can also set a different status for the notification besides job failure.  Here is a list of the codes that would go into the @notify_level_email value.

Value Description
0 Never
1 On Success
2 On Failure
3 Always

In addition to setting an email alert you have also have the ability to send alerts via netsend and pager. Here are the options to use, they also use the same values as in the table above:

  • @notify_level_email (you must also set  @notify_email_operator_name)
  • @notify_level_netsend (you must also set  @notify_netsend_operator_name)
  • @notify_level_page (you must also set  @notify_page_operator_name)

Next Steps

  • Take the next step and change the query to automatically do the updates instead of just giving you the T-SQL code
  • Look at ways to incorporate multiple servers into the process
  • Setup SQLMail and setup operators, alerts and notifications to run on your server to notify you of job failures


Last Updated: 2006-10-26


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Saturday, October 05, 2013 - 5:50:07 PM - smt Back To Top

I deleted Log Shipping job, including all alerts on primary and secondary. but alert still being fired saying "resotre job out of sync" can you help me to clean the alert or remove it


Wednesday, March 13, 2013 - 12:25:42 PM - Brano Back To Top

Thanks Greg,

Very helpful article.

Regards,

Brano


Wednesday, December 14, 2011 - 5:51:20 PM - Greg Robidoux Back To Top

If you setup an email group on your email server and then you can send the email to the email group instead of the individual users.

 


Wednesday, December 14, 2011 - 5:38:47 PM - Ankit Shah Back To Top

Nice post about setting up alert.hope you can help me out with my scenario .

i have created group SQL notification with 3 different users. i want to notify them job status once the job finishes but in job notification you can supply only one e-mail or even in alert is same so do you have any ideas how to setup with this?

Thanks


Learn more about SQL Server tools