Oops… A Lot Of Trees… Jobs to Update

Yesterday I posted about having an alert in place to know when SQL Agent jobs don’t have operators setup.  I’ve found this alert to be extremely useful, but often when its been shared it just leads to junk e-mail.

The reason for this is when the alert is added to 5, 10, or 50 servers it usually brings back a large number of results the first couple times.  And all of the jobs listed need to be updated so that the alerts start going out as required.

To alleviate the pain that the first execution can create, a maintenance procedure is added to your DBA database which allows for mass or bulk updating of all of the SQL Agent jobs on the SQL Server instance.

The stored procedure accepts the following parameters:

• @operator: The name of the SQL Agent Operator that should receive the alerts.
• @notify_level_email: The type of notifications the job should send to e-mail.  The values for these can be found in help for sp_update_job.  The value 2 translates to On Failure.
• @notify_level_eventlog: The type of notification the job should send to the event log.  The value 0 translates to Never.

IF NOT EXISTS(SELECT * FROM sys.schemas WHERE name = 'Maintenance')
EXEC('CREATE SCHEMA [Maintenance] AUTHORIZATION [dbo]')
GO
IF OBJECT_ID('Maintenance.SQLAgentJobsNotifyUpdate') IS NOT NULL
DROP PROCEDURE Maintenance.SQLAgentJobsNotifyUpdate
GO
/*============================================================
Procedure: [Maintenance].[SQLAgentJobsNotifyUpdate]
Author: Jason Strate
Date: June 1, 2009
Synopsis:
Mass update all jobs without notifications setup with
new settings.
Exec [Maintenance].[SQLAgentJobsNotifyUpdate]
@operator = 'DBA'
,@notify_level_email = 2
,@notify_level_eventlog = 0
============================================================
Revision History:
Date: By Description
------------------------------------------------------------
============================================================*/
CREATE PROCEDURE Maintenance.SQLAgentJobsNotifyUpdate
(
@operator sysname
,@notify_level_email int = 2
,@notify_level_eventlog int = 0
)
AS
DECLARE @job_id uniqueidentifier
DECLARE JOB_UPDATE CURSOR FAST_FORWARD FOR
SELECT job_id
FROM msdb.dbo.sysjobs
WHERE notify_email_operator_id = 0
AND notify_level_eventlog = 0
AND enabled = 1
OPEN JOB_UPDATE
FETCH NEXT FROM JOB_UPDATE INTO @job_id
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC msdb.dbo.sp_update_job
@job_id=@job_id
,@notify_level_email=@notify_level_email
,@notify_level_eventlog=@notify_level_eventlog
,@notify_email_operator_name=@operator
FETCH NEXT FROM JOB_UPDATE INTO @job_id
END
CLOSE JOB_UPDATE
DEALLOCATE JOB_UPDATE

CAUTION:  Since the procedure updates all jobs that don’t have notifications setup, check and double check before running this in a production environment to verify that the update it will do is exactly what you are looking for.