Auto alert for SQL Agent jobs when they are enabled or disabled

By: Alan Cranfield   |   Updated: 2009-07-28   |   Comments (27)   |   Related: > SQL Server Agent

Problem

So it turns out the reason that some of your batch process never ran or why you don't have a current backup or why your transaction logs filed up is because someone or something disabled a SQL job for some reason and neglected to re-enable it. SQL jobs can be disabled for any number of reasons, but they can cause real problems when they go unnoticed as disabled (or enabled as the case may be). It's often the case in large DBA teams where multiple DBAs can administer the same SQL Servers and it's not apparent to one DBA the importance of some of the jobs to another DBA. This is especially true of teams who work shifts or run a "follow the sun" support model. I've seen jobs with a "_do not enable!" or a "_disabled by Mark..." etc appendix. This gets quite messy especially on servers with lots of jobs. Wouldn't it be great if the whole DBA team got some form of notification or a heads up whenever someone disabled or enabled a SQL Job...?

Solution

Job information is held in the sysjobs table in the msdb database. The [enabled] field acts as switch to enable and disable a job. All we have to do is create a standard UPDATE TRIGGER on the msdb..sysjobs that will email us whenever the [enabled] field is updated. Greater visibility to other team members when jobs are disabled can help indentify and resolve possible issues before they turn into problems.

Also, for those cowboy DBAs amongst us, it may act as a deterrent to making unauthorized changes knowing that everyone in the team will be notified when a particular job is disabled or enabled.

The Trigger

The code below assumes that you have Database Mail configured. The Mail profile name and the email address of the DBA or support team are hardcoded - you'll need to edit as appropriate to your environment.

USE msdb  
GO  
CREATE TRIGGER tr_SysJobs_enabled  
ON sysjobs  
FOR UPDATE AS  
----------------------------------------------------------------------------  
-- Object Type : Trigger  
-- Object Name : msdb..tr_SysJobs_enabled  
-- Description : trigger to email DBA team when a job is enabled or disabled  
-- Author : www.mssqltips.com  
-- Date : July 2009  
----------------------------------------------------------------------------  
SET NOCOUNT ON  

DECLARE @UserName VARCHAR(50),  
@HostName VARCHAR(50),  
@JobName VARCHAR(100),  
@DeletedJobName VARCHAR(100),  
@New_Enabled INT,  
@Old_Enabled INT,  
@Bodytext VARCHAR(200),  
@SubjectText VARCHAR(200), 
@Servername VARCHAR(50) 

SELECT @UserName = SYSTEM_USER, @HostName = HOST_NAME()  
SELECT @New_Enabled = Enabled FROM Inserted  
SELECT @Old_Enabled = Enabled FROM Deleted  
SELECT @JobName = Name FROM Inserted  
SELECT @Servername = @@servername 

-- check if the enabled flag has been updated. 
IF @New_Enabled <> @Old_Enabled  
BEGIN  

  IF @New_Enabled = 1  
  BEGIN  
    SET @bodytext = 'User: '+@username+' from '+@hostname+
        ' ENABLED SQL Job ['+@jobname+'] at '+CONVERT(VARCHAR(20),GETDATE(),100)  
    SET @subjecttext = @Servername+' : ['+@jobname+
        '] has been ENABLED at '+CONVERT(VARCHAR(20),GETDATE(),100)  
  END  

  IF @New_Enabled = 0  
  BEGIN  
    SET @bodytext = 'User: '+@username+' from '+@hostname+
        ' DISABLED SQL Job ['+@jobname+'] at '+CONVERT(VARCHAR(20),GETDATE(),100)  
    SET @subjecttext = @Servername+' : ['+@jobname+
        '] has been DISABLED at '+CONVERT(VARCHAR(20),GETDATE(),100)  
  END  

  SET @subjecttext = 'SQL Job on ' + @subjecttext  

  -- send out alert email 
  EXEC msdb.dbo.sp_send_dbmail  
  @profile_name = 'Default', --<<< insert your Mail Profile here 
  @recipients = '[email protected]', --<<< insert your team email here 
  @body = @bodytext,  
  @subject = @subjecttext  

END 

Practice

Now, whenever a job is disabled or enabled the trigger will fire and send through an email providing useful information about the event:

Next Steps

• Trigger can be expanded to incorporate email alerts when jobs are dropped or new jobs created. An audit table can be implemented to track change. I'll deal with these in a future tip. 
• Here are some additional tips related to SQL Agent
  • Disabling (or Enabling) SQL Server Agent Jobs
  • Analyzing SQL Agent Job and Job Step History in SQL Server
  • Different ways to execute a SQL Agent job
  • An alternative to SQL Server email alerts
  • Generate SQL Agent Job Schedule Report

About the author

Alan Cranfield is a versatile SQL Server DBA with over 10 years experience managing critical systems in large distributed environments.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips