Monitoring SQL Server Jobs: Script to Report Failed Jobs

Managing and monitoring SQL Server jobs is crucial for maintaining the health and performance of your SQL Server environment. One of the key aspects is to ensure that failed jobs are promptly reported and addressed. Below is a script that creates a report of failed SQL Server jobs and sends it via email. This script can be particularly useful for DBAs to automate the monitoring process.

Script Overview

The script performs the following tasks:

1. Create a Table: A temporary table Failed_Jobs is created to store details of the failed jobs.
2. Insert Data: Job failure details are inserted into the Failed_Jobs table. This includes jobs that failed in the last 24 hours.
3. Check for Failures: The script checks if there are any failed jobs.
4. Send Email: If there are any failures, an HTML-formatted email is sent, listing the details of the failed jobs.
5. Drop Table: The temporary table is dropped at the end of the script.

Here’s the complete script:

CREATE TABLE Failed_Jobs (

      NOT NULL,

      NULL,

      NULL,

      NULL,

     NULL,

      NULL

);

INSERT INTO Failed_Jobs

SELECT 'FAILED' AS Status, 

       CAST(sj.name AS varchar(100)) AS "Job Name",

       CAST(sjs.step_id AS varchar(5)) AS "Step ID",

       CAST(sjs.step_name AS varchar(30)) AS "Step Name",

       CAST(REPLACE(CONVERT(varchar, CONVERT(datetime, CONVERT(varchar, sjh.run_date)), 102), '.', '-') + ' ' +

       SUBSTRING(RIGHT('000000' + CONVERT(varchar, sjh.run_time), 6), 1, 2) + ':' +

       SUBSTRING(RIGHT('000000' + CONVERT(varchar, sjh.run_time), 6), 3, 2) + ':' +

       SUBSTRING(RIGHT('000000' + CONVERT(varchar, sjh.run_time), 6), 5, 2) AS varchar(30)) 'Start Date Time',

       sjh.message AS "Message"

FROM sysjobs sj

JOIN sysjobsteps sjs ON sj.job_id = sjs.job_id

JOIN sysjobhistory sjh ON sj.job_id = sjh.job_id AND sjs.step_id = sjh.step_id

WHERE sjh.run_status <> 1

  AND CAST(sjh.run_date AS float) * 1000000 + sjh.run_time > 

      CAST(CONVERT(varchar(8), GETDATE() - 1, 112) AS float) * 1000000 + 70000 -- yesterday at 7am

UNION

SELECT 'FAILED', 

       CAST(sj.name AS varchar(100)) AS "Job Name",

       'MAIN' AS "Step ID",

       'MAIN' AS "Step Name",

       CAST(REPLACE(CONVERT(varchar, CONVERT(datetime, CONVERT(varchar, sjh.run_date)), 102), '.', '-') + ' ' +

       SUBSTRING(RIGHT('000000' + CONVERT(varchar, sjh.run_time), 6), 1, 2) + ':' +

       SUBSTRING(RIGHT('000000' + CONVERT(varchar, sjh.run_time), 6), 3, 2) + ':' +

       SUBSTRING(RIGHT('000000' + CONVERT(varchar, sjh.run_time), 6), 5, 2) AS varchar(30)) 'Start Date Time',

       sjh.message AS "Message"

FROM sysjobs sj

JOIN sysjobhistory sjh ON sj.job_id = sjh.job_id

WHERE sjh.run_status <> 1 AND sjh.step_id = 0

  AND CAST(sjh.run_date AS float) * 1000000 + sjh.run_time > 

      CAST(CONVERT(varchar(8), GETDATE() - 1, 112) AS float) * 1000000 + 70000 -- yesterday at 7am

DECLARE @cnt INT  

SELECT @cnt = COUNT(1) FROM Failed_Jobs    

IF (@cnt > 0)

BEGIN

    DECLARE @strsubject VARCHAR(100)

    SELECT @strsubject = 'Check the following failed jobs on ' + @@SERVERNAME

    DECLARE @tableHTML NVARCHAR(MAX);

    SET @tableHTML =

        N'<H1>Failed Jobs Listing - ' + @@SERVERNAME + '</H1>' +

        N'<table border="1">' +

        N'<tr><th>Status</th><th>Job Name</th>' +

        N'<th>Step ID</th><th>Step Name</th><th>Start Date</th>' +

        N'<th>Message</th></tr>' +

        CAST(( 

            SELECT 

                td = [Status], '',

                td = [Job Name], '',

                td = [Step ID], '',

                td = [Step Name], '',

                td = [Start Date Time], '',

                td = [Message]

            FROM Failed_Jobs

            FOR XML PATH('tr'), TYPE 

        ) AS NVARCHAR(MAX)) +

        N'</table>';

    EXEC msdb.dbo.sp_send_dbmail

        @from_address = 'BANDARUAJEYUDU@OUTLOOK.com',

        @recipients = 'BANDARUAJEYUDU@OUTLOOK.com',

        @subject = @strsubject,

        @body = @tableHTML,

        @body_format = 'HTML',

        @profile_name = 'SQLMail'

END

DROP TABLE Failed_Jobs;

Explanation

1. Create Table: The table Failed_Jobs is created with columns to hold status, job name, step ID, step name, start date time, and message.
2. Insert Data: The INSERT INTO statements retrieve job failure details from sysjobs, sysjobsteps, and sysjobhistory tables.
3. Check for Failures: The script counts the number of rows in Failed_Jobs and proceeds if there are any failures.
4. Send Email: An HTML email is constructed with the job failure details and sent using sp_send_dbmail.
5. Drop Table: The Failed_Jobs table is dropped at the end of the script.

By automating the reporting of failed jobs, DBAs can quickly address issues and ensure the smooth operation of SQL Server jobs.