Script to find failed SQL Server Agent Jobs

On 28 May, 2015

MS SQL DBA

By : Charith Silva

No Comments

It’s handy to have a script to find failed SQL Server Agent Jobs. Most of the organizations execute resource intensive SQL process such as backup, heavy ETL process at night and it is essential to validate those import process daily basis. Also it’s better to identify the failure jobs in advance rather than users are raising issues.

DECLARE @Date datetime 
SET @Date = DATEADD(dd, -2, GETDATE()) -- Last 2 days


SELECT j.[name] [Agnet_Job_Name], 
js.step_name [Step_name], 
js.step_id [Step ID], 
js.command [Command_executed],
js.database_name [Databse_Name],
msdb.dbo.agent_datetime(h.run_date, h.run_time) as [Run_DateTime] ,
h.sql_severity [Severity], 
h.message [Error_Message], 
h.server [Server_Name],
h.retries_attempted [Number_of_retry_attempts],
CASE h.run_status 
  WHEN 0 THEN 'Failed'
  WHEN 1 THEN 'Succeeded'
  WHEN 2 THEN 'Retry'
  WHEN 3 THEN 'Canceled'
END as [Job_Status],
CASE js.last_run_outcome
  WHEN 0 THEN 'Failed'
  WHEN 1 THEN 'Succeeded'
  WHEN 2 THEN 'Retry'
  WHEN 3 THEN 'Canceled'
  WHEN 5 THEN 'Unknown'
END as [Outcome_of_the_previous_execution]
FROM msdb.dbo.sysjobhistory h 
INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id 
INNER JOIN msdb.dbo.sysjobsteps js ON j.job_id = js.job_id AND h.step_id = js.step_id
WHERE h.run_status = 0 AND msdb.dbo.agent_datetime(h.run_date, h.run_time)> @Date
ORDER BY h.instance_id DESC

DECLARE @Date datetime

SET @Date = DATEADD(dd, -2, GETDATE()) -- Last 2 days

SELECT j.[name] [Agnet_Job_Name],

js.step_name [Step_name],

js.step_id [Step ID],

js.command [Command_executed],

js.database_name [Databse_Name],

msdb.dbo.agent_datetime(h.run_date, h.run_time) as [Run_DateTime] ,

h.sql_severity [Severity],

h.message [Error_Message],

h.server [Server_Name],

h.retries_attempted [Number_of_retry_attempts],

CASE h.run_status

WHEN 0 THEN 'Failed'

WHEN 1 THEN 'Succeeded'

WHEN 2 THEN 'Retry'

WHEN 3 THEN 'Canceled'

END as [Job_Status],

CASE js.last_run_outcome

WHEN 0 THEN 'Failed'

WHEN 1 THEN 'Succeeded'

WHEN 2 THEN 'Retry'

WHEN 3 THEN 'Canceled'

WHEN 5 THEN 'Unknown'

END as [Outcome_of_the_previous_execution]

FROM msdb.dbo.sysjobhistory h

INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id

INNER JOIN msdb.dbo.sysjobsteps js ON j.job_id = js.job_id AND h.step_id = js.step_id

WHERE h.run_status = 0 AND msdb.dbo.agent_datetime(h.run_date, h.run_time)> @Date

ORDER BY h.instance_id DESC

About The Author

Charith Silva

Charith Silva is a Microsoft certified SQL Server developer and database administrator who was graduated at Buckinghamshire New university in the UK. His career was started in 1998, primarily into Web application development, and later diversified into database development. He has got a vast experience in SQL Server database development, Database administration and Business Intelligence development. He believes that sharing the knowledge is key to the success.

Number of Posts : 87

All Posts by : Charith Silva