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


By:   |   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 = 'acranfield@mssqltips.com', --<<< 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:

sample email
Next Steps





get scripts

next tip button



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

View all my tips


Article Last Updated: 2009-07-28

Comments For This Article




Tuesday, October 26, 2021 - 12:31:51 PM - Devendra Kumar Sahu Back To Top (89363)
it's working

Wednesday, January 22, 2020 - 9:39:44 AM - Greg Robidoux Back To Top (83929)

Hi Sean, you could use a server scoped DDL trigger to capture the CREATE DATABASE.  Not so sure about the ATTACH DATABASE.

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-ver15


Tuesday, January 21, 2020 - 4:54:22 PM - Sean Perkins Back To Top (83916)

Is there a tip you have that has a version of this that will email my team when a database is created or attached?


Saturday, December 7, 2019 - 2:04:54 PM - Raphael Ferreira Back To Top (83320)

Very useful. THANKS!


Tuesday, December 3, 2019 - 8:23:23 AM - John Rewcastle Back To Top (83265)

Alan,

This worked PERFECTLY! I do have another question. We have had instances where SQL Server Agent didn't start after a recovery or quit working. Is there a way to get an email notification for this? What most people don't use is sending it as 4235551212@vtext.com to get a text instead of an email.


Thursday, July 25, 2019 - 12:23:42 PM - Slightly Peeved DBA Back To Top (81866)

My team just had to clean up after this script on an instance where we couldnt set up log shipping after a DR failover.  This trigger had been previously installed by an applications management team, but the dbmail functionality disabled as the outcome of a pen testing report. 

Long story short, the trigger will throw an error in these cirumstances, breaking the SSMS functionality for manipulation of SQL agent jobs.

MS Documentation states "SQL Server does not support triggers defined on the system tables, because they might modify the operation of the system."
https://docs.microsoft.com/en-us/sql/relational-databases/databases/system-databases?view=sql-server-2017

I think everyone should be aware of the potential for knock on issues like this as well as the support implications before implementing something like this.


Monday, August 20, 2018 - 4:00:20 AM - Hans Lindgren Back To Top (77242)

Great idea! Thanks :)


Thursday, August 31, 2017 - 8:59:02 AM - Greg Robidoux Back To Top (65656)

Hi Jagan,

The best way to do this is to add some code to the trigger to check which category the job is in and if it is in a category you want to skip then just exit the process. 

You would need to make sure the jobs you want to skip are categorized accordingly.

See this for job categories: https://www.mssqltips.com/sqlservertip/1484/custom-job-categories-to-organize-your-sql-agent-jobs/

-Greg


Thursday, August 31, 2017 - 8:10:35 AM - Jagan Back To Top (65654)

 

 

Please anyone help me on the previous post..

Thanks,

Jagan


Thursday, August 24, 2017 - 6:43:55 AM - Jagan Back To Top (65231)

Hi, 

Thanks for the wonderful post...

Also i want to exclude some jobs from notifications.. Is there any way to exclude some of the jobs (Replication, Logshipping jobs) from the alert.
I just want alert for some of the jobs when disabled or enabled...

Please help on this...

Thank you

 


Wednesday, August 16, 2017 - 1:55:02 PM - Ankit Shah Back To Top (64921)

 HI Alan,

 This is very helpful script and thank you for working on this. I was wondering to add alert for SQL job create and drop as well so everything in one script. 

any suggestions on this?

Thank you.

 

 


Thursday, September 29, 2016 - 2:26:26 PM - Allan Tens Back To Top (43459)

Hi Alan,

 

Will this trigger have a bad effect in any way on the performance of the msdb database?

 

Thanks,

Allan.


Thursday, August 13, 2015 - 8:22:21 AM - Troy Back To Top (38438)

I did something similar but with a slightly different twist.  I'm of the ilk that there should not ever be disabled jobs in my agent.  Thusly I created a job that fires 4x daily simply listing all currently disabled jobs.  Thuis way if I don't get around to fixing it right away, I will be reminded again in 6 hours.  A single trigger as above is nice, but if I'd be worried that if I was otherwise occupied, I might forget about it.

The job step code is extremely basic...

 

if (select count(*) from sysjobs

where enabled = 0) > 0

 

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'SQLMail',

@recipients = 'dbaoncall@myoffice.com',

@query = 'select [name] [Disabled Job Name] from msdb.dbo.sysjobs

where enabled = 0

order by [name]' ,

@subject = 'Disabled jobs report',

@attach_query_result_as_file = 0 ;


Tuesday, December 2, 2014 - 8:29:51 PM - Siva Back To Top (35469)

Thank you for sharing the code. This is very helpful and makes my life easier to monitor 100+ instances. Keep up good work 


Monday, July 14, 2014 - 2:19:40 PM - SQL_Jobs Back To Top (32696)

I ike to get this notification in a table ( Store In a Table )  not in an email ,  I will probably not have it send an email out... where should i create the table and how should do that?


Wednesday, May 22, 2013 - 12:49:30 PM - Sergio Pacheco Back To Top (24082)

--Great Script!  I decided it to create a trigger based on it to send an email if someone decided to disabled/enable a job schedule.

USE msdb 
GO 
CREATE TRIGGER tr_SysSchedules_enabled 
ON sysschedules 
FOR UPDATE AS 
---------------------------------------------------------------------------- 
-- Object Type : Trigger 
-- Object Name : msdb..tr_SysSchedules_enabled 
-- Description : trigger to email DBA team when a job is enabled or disabled 
-- Author : Sergio Pacheco with help from www.mssqltips.com ; )
-- Date : May 23, 2013
---------------------------------------------------------------------------- 
SET NOCOUNT ON 

DECLARE @username VARCHAR(50), 
@hostname VARCHAR(50), 
@schedulename 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 @schedulename = 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 Schedule ['+@schedulename+'] at '+CONVERT(VARCHAR(20),GETDATE(),100) 
    SET @subjecttext = @Servername+' : ['+@schedulename+
        '] has been ENABLED at '+CONVERT(VARCHAR(20),GETDATE(),100) 
  END 

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

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

  -- send out alert email
  EXEC msdb.dbo.sp_send_dbmail 
  @profile_name = 'YourServerMailProfile', --<<< insert your Mail Profile here
  @recipients = 'DBA_Notify@YourOrg.org', --<<< insert your team email here
  @body = @bodytext, 
  @subject = @subjecttext
 
  END 


Thursday, September 13, 2012 - 10:46:24 AM - Alicia Back To Top (19506)

Thanks for this,exactly what I was looking for.

Cheers


Monday, August 27, 2012 - 1:53:10 AM - Prasadi Back To Top (19233)

Really fabulous blog. It worked for me. Great work, very simple, effective and consistancy was there.

Thanks a lot, Keep it up.

 

 


Thursday, June 14, 2012 - 3:31:01 AM - Alan Cranfield Back To Top (17983)

Thats what the tip is all about... You need to set a trigger to alert you... This info is not saved anywhere by default.


Wednesday, June 13, 2012 - 11:44:39 AM - Maciek Back To Top (17973)

Real nice article. May I ask you how to check when sql job was enabled/disabled and by whom?

 

 


Monday, May 23, 2011 - 9:15:17 PM - sherrie Back To Top (13896)

Hey thanks for getting back with me. I’m running SQL server 2008 R2 and I’ve been told that since I indeed have admin privs on msdb but others in my group do not (yet they can enable and disable SQL agent jobs) is the reason why I don’t get notified of their updates. Outside of granting them admin privs is there any other way to capture their login/workstation ids in the email?


Monday, May 23, 2011 - 4:54:29 AM - Alan Back To Top (13888)

hmm, thats no good - the whole idea is to be notified when others make changes :)... what version of SQL are you running? are you a sysadmin...


Thursday, May 19, 2011 - 1:36:44 PM - sherrie Back To Top (13867)

Nice itility. One quick question. I do get notified if I disable or enable jobs but fail to get notified when others disable or enable jobs. Any ideas?


Wednesday, September 15, 2010 - 5:56:19 PM - Ken Back To Top (10161)
Great Article!!! I really need the trigger to audit deletion of SQL jobs.  Can you please show the code for the trigger to audit this?

Thank you,

Ken


Tuesday, September 29, 2009 - 2:15:31 PM - lgomez Back To Top (4107)

This was a very useful script for jobs being enabled or disabled.  Do you have a script that would go one level deep in regards to schedules being enabled or disabled?  Thank you very much.


Thursday, August 13, 2009 - 7:51:02 AM - doclane Back To Top (3874)

Thank you!  I have a very distributed environment in which I am unable to monitor all servers and I never thought to add a trigger to an msdb table. So simple!


Tuesday, July 28, 2009 - 4:46:17 AM - Baltchic Back To Top (3786)

Hi Alan,

Thank you for a most useful tip - simplistic and yet effective.  Nicely done!

-Marti



download














get free sql tips
agree to terms