Setting Up Alerts for All SQL Server Agent Jobs

By: Greg Robidoux   |   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:

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.

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

About the author

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

View all my tips