Today, I’m sharing the following T-SQL script which you can use to find which SQL Server Agent Jobs failed yesterday. I use this script as part of my daily server monitoring SSIS package, which executes this script on all production SQL server and then sends email to our group:

SET NOCOUNT ON;

DECLARE @Value [varchar] (2048)
	,@JobName [varchar] (2048)
	,@PreviousDate [datetime]
	,@Year [varchar] (4)
	,@Month [varchar] (2)
	,@MonthPre [varchar] (2)
	,@Day [varchar] (2)
	,@DayPre [varchar] (2)
	,@FinalDate [int]
-- Declaring Table variable
DECLARE @FailedJobs TABLE ([JobName] [varchar](200))

-- Initialize Variables
SET @PreviousDate = DATEADD(dd, - 1, GETDATE())
SET @Year = DATEPART(yyyy, @PreviousDate)

SELECT @MonthPre = CONVERT([varchar](2), DATEPART(mm, @PreviousDate))

SELECT @Month = RIGHT(CONVERT([varchar], (@MonthPre + 1000000000)), 2)

SELECT @DayPre = CONVERT([varchar](2), DATEPART(dd, @PreviousDate))

SELECT @Day = RIGHT(CONVERT([varchar], (@DayPre + 1000000000)), 2)

SET @FinalDate = CAST(@Year + @Month + @Day AS [int])

-- Final Logic
INSERT INTO @FailedJobs
SELECT DISTINCT j.[name]
FROM [msdb].[dbo].[sysjobhistory] h
INNER JOIN [msdb].[dbo].[sysjobs] j
	ON h.[job_id] = j.[job_id]
INNER JOIN [msdb].[dbo].[sysjobsteps] s
	ON j.[job_id] = s.[job_id]
		AND h.[step_id] = s.[step_id]
WHERE h.[run_status] = 0
	AND h.[run_date] > @FinalDate

SELECT @JobName = COALESCE(@JobName + ', ', '') + '[' + [JobName] + ']'
FROM @FailedJobs

SELECT @Value = 'Failed SQL Agent job(s) found: ' + @JobName + '. '

IF @Value IS NULL
BEGIN
	SET @Value = 'None.'
END

SELECT @Value

I hope you will find this script useful 😉