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 accidently 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
USE [msdb]GO/* ======================================================Script By: Aasim Abdullah @http://connectsql.blogspot.comScript For: Create trigger on msdb.dbo.sysjobs table,to detect any change in job status by anyuser and mail it to DB team-- =================================================== */CREATE TRIGGER [dbo].[JobStatusAlert]ON [dbo].[sysjobs]AFTER UPDATEASBEGINSET NOCOUNT ON;-- Check if job is enabled/disabledDECLARE @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 ENDFROM deleted d INNER JOIN inserted iON d.job_id = I.job_id) = 1BEGIN-- Get session detail and create a messageSELECT TOP 1 @MailBody = 'Job "'+i.name+'" is recently '+CASE WHEN i.enabled = 0THEN ' 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 cINNER JOIN sys.dm_exec_sessions s ON c.session_id = s.session_idCROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle)CROSS APPLY inserted iWHERE 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 TeamEXEC msdb.dbo.sp_send_dbmail@firstname.lastname@example.org', -- change mail address accordingly@subject = 'Job Status Changed at Client-101 DB Server',@profile_name = 'DBA TEAM', -- Change profile name accordingly@body = @MailBody;ENDEND