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.
CREATE TABLE #Failed_Jobs (
Status VARCHAR(10) NOT NULL,
[Job Name] VARCHAR(100) NOT NULL,
[Step ID] VARCHAR(5) NOT NULL,
[Step Name] VARCHAR(30) NOT NULL,
[Start Date Time] VARCHAR(30) NOT NULL,
Message NVARCHAR(MAX) NOT NULL
);
-- Insert failed job details
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), '.', '-') + ' ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR, sjh.run_time), 6), 3, 0, ':'), 6, 0, ':')
AS VARCHAR(30)) AS "Start Date Time",
sjh.message AS "Message"
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobsteps sjs ON sj.job_id = sjs.job_id
JOIN msdb.dbo.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 7 AM
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), '.', '-') + ' ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR, sjh.run_time), 6), 3, 0, ':'), 6, 0, ':')
AS VARCHAR(30)) AS "Start Date Time",
sjh.message AS "Message"
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.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 7 AM
-- Check if there are failed jobs
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" cellspacing="0" cellpadding="4">' +
N'<tr><th>Status</th><th>Job Name</th><th>Step ID</th>' +
N'<th>Step Name</th><th>Start Date</th><th>Message</th></tr>' +
CAST((
SELECT
N'<tr><td>' + Status +
N'</td><td>' + [Job Name] +
N'</td><td>' + [Step ID] +
N'</td><td>' + [Step Name] +
N'</td><td>' + [Start Date Time] +
N'</td><td>' + Message + N'</td></tr>'
FROM #Failed_Jobs
FOR XML PATH(''), 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 temp table
DROP TABLE #Failed_Jobs;
By automating the reporting of failed jobs, DBAs can quickly address issues and ensure the smooth operation of SQL Server jobs.
No comments:
Post a Comment