USE [msdb]
GO
IF OBJECT_ID('dbo.usp_FailedJobs') IS NULL
EXEC ('CREATE PROCEDURE dbo.usp_FailedJobs AS SELECT 1 AS ID')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE dbo.usp_FailedJobs
@Summary BIT = 1,
@JobNameLike VARCHAR(256) = NULL, -- 'Diablo.DataLoad.LoadInitial 04'
@StepNameLike VARCHAR(256) = NULL, -- 'LoadInitial'
@DaysBefore TINYINT = 7,
@FilterLineLike VARCHAR(1000) = NULL, --' Description: An error occurred while processing file "%" on data row %.'
@ReturnAllIfFilterMatched BIT = 0
AS
SET NOCOUNT ON
IF @Summary = 1
BEGIN
------------------------------------------------- JOB FAILURE SUMMARY -------------------------------------------------
SELECT j.name AS job_name,
jh.step_name,
SUM(jh.run_duration) AS run_duration,
COUNT(*) AS Counter
FROM msdb.dbo.sysjobhistory jh (NOLOCK)
INNER JOIN [msdb].[dbo].[sysjobs] j (NOLOCK) ON jh.job_id = j.job_id
WHERE jh.run_status = 0
AND jh.run_date >= CAST(CONVERT(VARCHAR(8), DATEADD(DAY, -1 * @DaysBefore, GETDATE()), 112) AS INT)
AND jh.step_id > 0
GROUP BY j.name,
jh.step_name
ORDER BY COUNT(*) DESC
PRINT
'EXEC msdb.dbo.usp_FailedJobs
@Summary = 0,
@JobNameLike = NULL, --''Diablo.DataLoad.LoadInitial%'',
@StepNameLike = NULL, --''%Initial%'',
@DaysBefore = 7,
@FilterLineLike = NULL --''%An error occurred while processing file "%" on data row %.''
'
END
------------------------------------------------- JOB FAILURE DETAIL -------------------------------------------------
ELSE
BEGIN
WITH criteria
AS (SELECT j.name
AS job_name,
step_name,
step_id,
jh.job_id,
run_date,
run_time,
CAST(CAST([run_date]
AS VARCHAR)
AS DATETIME) + CAST(CAST(STUFF(STUFF(RIGHT('0' + CAST([run_time]
AS VARCHAR), 6), 3, 0, ':'), 6, 0, ':')
AS TIME)
AS DATETIME)
AS RunDateTime,
run_duration
FROM msdb.dbo.sysjobhistory jh (NOLOCK)
INNER JOIN [msdb].[dbo].[sysjobs] j (NOLOCK) ON jh.job_id = j.job_id
WHERE run_status = 0
AND jh.step_id > 0
AND (j.name LIKE @JobNameLike
OR @JobNameLike IS NULL)
AND (step_name LIKE @StepNameLike
OR @StepNameLike IS NULL)
AND run_date >= CAST(CONVERT(VARCHAR(8), DATEADD(DAY, -1 * @DaysBefore, GETDATE()), 112)
AS INT))
,s2
AS (SELECT TOP 999999 a.job_id,
a.MESSAGE,
c.job_name,
c.step_name,
c.RunDateTime,
c.run_duration
FROM msdb.dbo.sysjobhistory a (NOLOCK)
INNER JOIN criteria c ON a.step_id = c.step_id
AND a.job_id = c.job_id
AND a.run_date = c.run_date
AND a.run_time = c.run_time
WHERE a.run_status IN (0, 4)
AND a.step_id > 0
ORDER BY instance_id)
,s3
AS (SELECT 'job_name: ' + job_name + '
step_name: ' + step_name + '
run_start: ' + CONVERT(VARCHAR(30), RunDateTime, 120) + '
run_duration: ' + CAST(run_duration
AS VARCHAR) + '
' + Util.dbo.StringConcat(NewMessage, '
')
AS msg
FROM s2
CROSS APPLY (SELECT
CASE
WHEN @FilterLineLike <> ''
AND @ReturnAllIfFilterMatched = 0 THEN (SELECT Util.dbo.StringConcat(field, '
')
FROM (SELECT field
FROM Util.dbo.ParseDelimited(MESSAGE, '
') p
WHERE Field LIKE @FilterLineLike) k)
WHEN @FilterLineLike <> ''
AND @ReturnAllIfFilterMatched = 1 THEN
CASE
WHEN MESSAGE LIKE @FilterLineLike THEN MESSAGE
END
ELSE MESSAGE
END NewMessage) m
GROUP BY job_id,
job_name,
RunDateTime,
step_name,
run_duration)
,s4
AS (SELECT Util.dbo.StringConcat(msg, '
------------- End -------------
')
AS msg
FROM s3)
SELECT b.Field AS '------ Job step error detail ------'
FROM s4
CROSS APPLY Util.dbo.ParseDelimited(msg, '
') b
ORDER BY FieldNum
END
GO
EXEC sys.sp_ms_marksystemobject usp_FailedJobs
GO