Setting Up Alerts for All SQL Server Agent Jobs

By:   |   Updated: 2022-02-25   |   Comments (4)   |   Related: > SQL Server Agent


Problem

One of the things you can do with SQL Server 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 SQL Agent jobs is that you need to edit each 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. Luckily there is a stored procedure that can help make this task easier.

Solution

As with everything you can do through SQL Server Management Studio 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 the GUI. Before we begin you need to already have Database Mail setup in order to use this as well as have an operator setup to receive the email alert.

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:

sql agent job notification settings

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 a query windows 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 review the commands 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 a query window and executed to make the actual updates to your jobs. After the commands are executed all of these jobs now have "SQLalerts" as the operator to get notified when any 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 can also 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 Database Mail and setup operators, alerts and notifications to run on your server to notify you of job failures





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


Article Last Updated: 2022-02-25

Comments For This Article




Saturday, October 5, 2013 - 5:50:07 PM - smt Back To Top (27054)

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 (22782)

Thanks Greg,

Very helpful article.

Regards,

Brano


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

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 (15359)

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















get free sql tips
agree to terms