T-SQL query to check failed SQL Agent Jobs in the Last 24 Hour

3 Oct, 2022

T-SQL query to check failed SQL Agent Jobs in the Last 24 Hour

Below query can be used to check SQL Agent failed jobs in last 24 hours. This query can be used in the monitoring script to find out failed job in SQL server agent.

SELECT sj.[name] AS "Failed Job Name", jh.run_date,jh.[message]
FROM msdb.dbo.sysjobhistory jh INNER JOIN msdb.dbo.sysjobs sj ON sj.job_id =
jh.job_id
WHERE jh.run_status=0 AND DATEDIFF(dd,cast (cast (jh.run_date AS VARCHAR(20)) AS
DATE),GETDATE())=0
GROUP BY sj.[name],jh.run_date,jh.message
GO

Sample Output: