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)
DECLARE @JobName  [varchar](2048)
DECLARE @PreviousDate [datetime]
DECLARE @Year   [varchar](4)
DECLARE @Month   [varchar](2)
DECLARE @MonthPre  [varchar](2)
DECLARE @Day   [varchar](2)
DECLARE @DayPre   [varchar](2)
DECLARE @FinalDate  [int]

-- Declaring Table variableDECLARE @FailedJobs TABLE ([JobName] [varchar](200))

-- Initialize VariablesSET @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 + @DayAS [int])

INSERTINTO @FailedJobs

-- Final LogicSELECTDISTINCT j.[name]
FROM [msdb].[dbo].[sysjobhistory] h
INNERJOIN [msdb].[dbo].[sysjobs] j
 ON h.[job_id] = j.[job_id]
INNERJOIN [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 * FROM @FailedJobs

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

I hope you will find this script useful