Click here to Skip to main content
Click here to Skip to main content

SQL Server: Keeping Log/Alert for Job Disable/Enable Status

, 1 Oct 2012
Rate this:
Please Sign up or sign in to vote.
How do you know, if someone disabled your job at production database server. Who is the calprit. How to send a mail alert for this change ??

Introduction

To monitor production database servers, Database Administrators create different jobs and depends upon these jobs to work for them i.e. to check if server have enough space, database is not corrupt, queries are not running slow, index defragmentation and many more. BUT what if somehow, someone accidentally disabled a job and forgot to enable it back. No alert will be created as job is disabled. Or it can be fatal when you need to restore a database and found that backup job was not working as it was disabled by someone .

Is there any way to get alert if someone changes any job status on production server?YES, by creating following trigger on msdb.dbo.sysjobs can resolve this problem. It will detect any change in job status and will mail a message like following to your DBA team.

Job "Daily Full Backup" is recently DISABLED by user aasim.abdullah with session id 167 and host name IdeaWrox-DB01 at Sep 12 2012 4:00:03:673AM

Script

USE [msdb]
GO
/* ======================================================
Script By: Aasim Abdullah @http://connectsql.blogspot.com
Script For: Create trigger on msdb.dbo.sysjobs table,
to detect any change in job status by any
user and mail it to DB team
-- =================================================== */
CREATE TRIGGER [dbo].[JobStatusAlert]
   ON [dbo].[sysjobs]
   AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;

-- Check if job is enabled/disabled
DECLARE @MailBody VARCHAR(300)
-- Check if job status is changed (enabled/disabled)
IF (SELECT TOP 1 CASE WHEN d.enabled = i.enabled THEN 0 ELSE 1 END
FROM deleted d INNER JOIN inserted i
ON d.job_id = I.job_id) = 1
BEGIN

-- Get session detail and create a message
SELECT TOP 1 @MailBody = 'Job "'
+i.name
+'" is recently '
+CASE WHEN i.enabled = 0
THEN ' DISABLED '
ELSE ' ENABLED 'END
+ ' by user '
+login_name
+ ' with session id '
+ CAST (c.session_id AS VARCHAR(3))
+' and host name '
+host_name +' at '
+ CONVERT(VARCHAR(50),last_request_end_time,109)
FROM sys.dm_exec_connections c
INNER JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle)
CROSS APPLY inserted i
WHERE text LIKE '%exec msdb.dbo.sp_help_job%'
AND text NOT LIKE '%SELECT c.session_id'
ORDER BY last_read DESC

-- Send mail to DBA Team
EXEC msdb.dbo.sp_send_dbmail
@recipients='essmess@gmail.com', -- change mail address accordingly
@subject = 'Job Status Changed at Client-101 DB Server',
@profile_name = 'DBA TEAM', -- Change profile name accordingly
@body = @MailBody;
END

END

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

aasim abdullah
Team Leader CureMD
Pakistan Pakistan
Aasim Abdullah is working as SQL Server DBA with CureMD (www.curemd.com) based in NY, USA. He has been working with SQL Server since 2007 (Version 2005) and has used it in many projects as a developer, administrator, database designer. Aasim's primary interest is SQL Server performance tuning. If he finds the time, he like to sketch faces with graphite pencils.
Follow on   Twitter   Google+

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 1 Oct 2012
Article Copyright 2012 by aasim abdullah
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid