Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Last Updated: 2009-07-28   |   Comments (21)   |   Related Tips: 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


next webcast button


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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

Great idea! Thanks :)


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

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

 

 

Please anyone help me on the previous post..

Thanks,

Jagan


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

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

 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

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

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

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

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

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

Thanks for this,exactly what I was looking for.

Cheers


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

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

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

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

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

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

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

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

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

Hi Alan,

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

-Marti


Learn more about SQL Server tools