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