By: Alan Cranfield | 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
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
