TSQL to find Failed Jobs

It can be frustrating to find recently failed jobs in the job history in SQL Server Management Studio. A quicker way to do it is to just run a query to see what jobs have failed recently.

The following query shows the jobs that have failed in the last 24 hours.

SELECT @@SERVERNAME,
j.name 'Job',
js.step_name,
jh.sql_severity,
jh.message,
msdb.dbo.agent_datetime(jh.run_date, jh.run_time) RunTime
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.sysjobsteps AS js ON js.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobhistory AS jh ON jh.job_id = j.job_id
WHERE jh.run_status = 0
AND msdb.dbo.agent_datetime(jh.run_date, jh.run_time) > GETDATE() - 1
ORDER BY msdb.dbo.agent_datetime(jh.run_date, jh.run_time) DESC;

This query has been tested on SQL Server 2005, SQL Server 2008, SQL Server 2008R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, and SQL Server 2019. It fails on SQL Server 2000 and older because of the agent_datetime function that didn’t exist prior to SQL Server 2005.

TSQL to find Failed Jobs

Leave a Reply Cancel reply