One of the essential tasks for a DBA team is to be notified when there is a job failure or an alert triggered in SQL Server. However, manually editing each job to enable notifications can be time-consuming, especially when dealing with multiple jobs and servers. Fortunately, SQL Server provides a stored procedure called sp_update_job that can simplify this process.
Sp_update_job is a stored procedure located in the msdb database, and it allows you to make changes to your jobs without using the GUI. By utilizing this stored procedure, you can update all jobs simultaneously, saving you valuable time and effort.
Before we begin, ensure that you have Database Mail set up and an operator configured to receive email alerts. Let’s assume we have an operator named “SQLalerts” and we want to update all jobs to use this operator in case of a job failure.
Instead of manually modifying each job, we can use the following T-SQL script to generate the necessary commands:
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 sysjobsWhen executed, this script will output the commands that can be copied and pasted into a query window for execution. Alternatively, you can modify the script to automatically issue the commands, but reviewing them before execution provides an additional layer of control.
After executing the generated commands, all jobs will be updated to use the “SQLalerts” operator for email notifications in case of failure. Additionally, you can customize the notification status by using different values for the @notify_level_email parameter. Here are the available options:
| Value | Description |
|---|---|
| 0 | Never |
| 1 | On Success |
| 2 | On Failure |
| 3 | Always |
In addition to email alerts, you can also configure notifications via netsend and pager. The options for these notifications use the same values as 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)
By leveraging the power of sp_update_job, you can streamline the process of setting up job notifications in SQL Server. This allows your DBA team to efficiently monitor job failures and alerts, ensuring the smooth operation of your database environment.
Article Last Updated: 2022-02-25