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


By:   |   Updated: 2009-07-28   |   Comments (26)   |   Related: More > 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 
  
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 
  
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



Last Updated: 2009-07-28


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
Related Resources





Comments For This Article




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 07, 2019 - 2:04:54 PM - Raphael Ferreira Back To Top (83320)

Very useful. THANKS!


Tuesday, December 03, 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 [email protected] 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 = '[email protected]',

@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 02, 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: '[email protected]+' from '[email protected]+
        ' ENABLED SQL Schedule ['[email protected]+'] at '+CONVERT(VARCHAR(20),GETDATE(),100) 
    SET @subjecttext = @Servername+' : ['[email protected]+
        '] has been ENABLED at '+CONVERT(VARCHAR(20),GETDATE(),100) 
  END 

  IF @New_Enabled = 0 
  BEGIN 
    SET @bodytext = 'User: '[email protected]+' from '[email protected]+
        ' DISABLED SQL Schedule ['[email protected]+'] at '+CONVERT(VARCHAR(20),GETDATE(),100) 
    SET @subjecttext = @Servername+' : ['[email protected]+
        '] 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 = '[email protected]', --<<< 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





Recommended Reading

Running a SSIS Package from SQL Server Agent Using a Proxy Account

Querying SQL Server Agent Job Information

Querying SQL Server Agent Job History Data

SQL Server Agent Job Schedule Reporting

Query SQL Server Agent Jobs, Job Steps, History and Schedule System Tables








get free sql tips
agree to terms


Learn more about SQL Server tools