Automating SQL Server Job Status Monitoring with Triggers

As a DBA, it's crucial to stay informed about changes in your SQL Server environment, especially when it comes to job status. Jobs being enabled or disabled can have significant impacts on operations, and timely alerts can help in taking necessary actions. Here, I’ll share a trigger script that automatically sends an email notification whenever a job is enabled or disabled in your SQL Server instance.

Script Overview

This trigger script is designed to:

1. Monitor Job Status Changes: It detects when a job is enabled or disabled.
2. Generate Alerts: It sends an email alert with details of the change, including the user who made the change, session ID, and host name.

Here's the complete trigger script:

USE [msdb]

GO

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='bajeyudu@SQLDBANOW.com', -- Change mail address accordingly

            @subject = 'Job Status Changed at SQLDBANOWDB01 Server',

            @profile_name = 'Sqlmail', -- Change profile name accordingly

            @body = @MailBody;

    END

END

GO

Explanation

1. Trigger Creation: The trigger JobStatusAlert is created on the sysjobs table and is set to execute after any update.
2. Detect Status Change: It checks if the job’s enabled status has changed by comparing the old and new values.
3. Construct Alert Message: If a change is detected, it constructs an alert message. The message includes:
   • Job name
   • Whether the job was enabled or disabled
   • User who made the change
   • Session ID and host name
   • Timestamp of the change
4. Send Email: The constructed message is sent via email to the DBA team using the sp_send_dbmail stored procedure.

Customization

• Email Address: Update the @recipients parameter in sp_send_dbmail with your DBA team’s email address.
• Profile Name: Ensure the @profile_name parameter matches the mail profile configured on your SQL Server.

Benefits

• Real-Time Alerts: Receive immediate notifications of job status changes, allowing you to take prompt action.
• Enhanced Monitoring: Automate the monitoring process, reducing the need for manual checks.
• Accountability: Track who made the changes, providing transparency and accountability in job management.

By implementing this trigger, you can ensure that no job status change goes unnoticed, thereby maintaining better control over your SQL Server jobs and minimizing potential disruptions.