Email Notification when a SQL Agent job status has changed.

We can setup email alerts whenever a SQL Server Agent job fails, completes or succeeds. But what do we do when a SQL job is disabled by someone. It can go unnoticed and lead to many problems.

Today I found that one of my production DB Backup jobs was disabled by someone and I did not know it for a few days.

Here is a trigger that you can use to send you an email whenver a job status is changed.

USE [msdb]
GO

/****** Object:  Trigger [dbo].[tu_sysjobs]    Script Date: 9/5/2012 5:09:21 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[tu_sysjobs] ON [dbo].[sysjobs]
FOR UPDATE
AS
/*
  Author : Siva Ramasamy
  Date   : 09/05/2012
 
  Description :  This trigger will send an email notification to the operator specified in the variable @v_operator_name whenever a job status is changed.
                 This trigger uses the default profile that is existing to send email notification.

  Pre Requisites : Database mail must be configured inorder for this trigger to fire.
 
*/
BEGIN  

    DECLARE @v_mail_body      VARCHAR(245)
    DECLARE @v_profile_name   VARCHAR(245)
    DECLARE @v_operator_name  VARCHAR(245)
    DECLARE @v_mail_subject   VARCHAR(245)


    SET @v_operator_name = '******PUT YOUR EMAIL ADDRESS HERE******'
    SET @v_mail_subject  = 'TMP PRODUCTION SQL Agent Job Status Change notification'

    SELECT    @v_profile_name = mp.name
    FROM      msdb.dbo.sysmail_profile mp
             INNER JOIN msdb.dbo.sysmail_principalprofile pp
             ON         mp.profile_id = pp.profile_id
    WHERE   pp.is_default = 1

    IF UPDATE(enabled)
    BEGIN      
        SELECT  @v_mail_body = 'Status of the job "'+
                              s.name + '" has changed from ' +
                              CASE d.ENABLED WHEN 1 THEN 'enabled'  ELSE 'disabled' END + ' to ' +
                              CASE s.ENABLED WHEN 1 THEN 'enabled.'  ELSE 'disabled.' END
        FROM  sysjobs s
        INNER JOIN DELETED d
        ON  s.job_id = d.job_id
        WHERE    s.enabled <> d.enabled

        PRINT 'profile name '+@v_profile_name

        EXEC msdb.dbo.sp_send_dbmail
             @profile_name = @v_profile_name,
             @recipients   = @v_operator_name,
             @body         = @v_mail_body ,
             @subject      = @v_mail_subject;
    END 
END
GO