How To Report SQL Jobs Failed report via email from SQL Server

[@kishorewesley1428]

Super brother

[@MSSQLDBATechSupport]

Thanks & keep Visiting

[@MUKESHKUMAR-si2ln]

Hi can you confirm what we can do to report the job failure from last 1 hour only instrad of 24 hours .

[@MSSQLDBATechSupport]

Hello Mukesh
Thanks for reaching us
We can achieve this in two ways.. we can filter within CTE or we can filter while inserting the data into the table
Here is the modified script and please test it and use accordingly
--==================================================
CREATE TABLE Failed_Jobs (
[Status] [varchar](Max) NOT NULL,
[Job Name] [varchar](Max) NULL,
[Step ID] [varchar](1000) NULL,
[Step Name] [varchar](Max) NULL, -- Increased the length to 100
[Start Date Time] [varchar](30) NULL,
[Message] [nvarchar](Max) NULL
)
GO
WITH LatestJobExecution AS (
SELECT
sj.name AS [Job Name],
sjs.step_id AS [Step ID],
sjs.step_name AS [Step Name],
sjh.run_status AS [Run Status],
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 datetime) AS [Start Date Time],
sjh.message AS [Message],
ROW_NUMBER() OVER (PARTITION BY sj.name, sjs.step_id ORDER BY sjh.run_date DESC, sjh.run_time DESC) AS RowNum
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 -- Failed jobs
AND sjh.run_date >= CONVERT(varchar(8), GETDATE(), 112) -- Jobs from today
AND sjh.run_time >= CONVERT(varchar(6), DATEADD(HOUR, -1, GETDATE()), 108) -- Jobs in the last one hour
),
FailedJobs AS (
SELECT
[Job Name],
[Step ID],
[Step Name],
[Run Status],
[Start Date Time],
[Message]
FROM
LatestJobExecution
WHERE
RowNum = 1
)
INSERT INTO Failed_Jobs ([Status], [Job Name], [Step ID], [Step Name], [Start Date Time], [Message])
SELECT
'FAILED' AS Status,
[Job Name],
[Step ID],
[Step Name],
CONVERT(varchar, [Start Date Time], 120) AS [Start Date Time],
[Message]
FROM
FailedJobs;
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'Failed Jobs Listing - ' + @@SERVERNAME + '' +
N'' +
N'StatusJob Name' +
N'Step IDStep NameStart Date' +
N'Message' +
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''
EXEC msdb.dbo.sp_send_dbmail
@recipients = '[e-mail]',
@subject = @strsubject,
@body = @tableHTML,
@body_format = 'HTML',
@profile_name = 'outlook'
END
GO
DROP TABLE Failed_Jobs

[@sivas842]

useful video

[@MSSQLDBATechSupport]

Thanks & keep Visiting

[@harishgoud1493]

Can you please share the script

[@MSSQLDBATechSupport]

All the scripts are shared in the below google Drive
drive.google.com/drive/folders/1vzcHXjQWuPmIRlB-QHbh37z4Qs_CrzUT?usp=sharing

[@suryap6518]

Link is not working

[@MSSQLDBATechSupport]

Links will get change... always check new videos description for new links

[@AminaluKunnath]

​@@MSSQLDBATechSupportnot able to access

[@MSSQLDBATechSupport]

Hello All
Please watch video and follow accordingly
ruclips.net/video/PPHFFaWbSiE/видео.htmlsi=zTWFZkxQsjubsJ-s