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 =
WHERE jh.run_status=0 AND DATEDIFF(dd,cast (cast (jh.run_date AS VARCHAR(20)) AS
GROUP BY sj.[name],jh.run_date,jh.message

Sample Output:

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.