check rerun status of failed/cancelled jobs
There are times when you have mulitple job failures and need to find out in a quick way which jobs/steps failed and what their rerun statuses are. This script creates a stored procedure in the msdb db to help you find out the statuses of these jobs.
use msdb
CREATE procedure sp_jobstatus_rerun_check
AS
select a.name as 'Job Name', b.step_name as 'Step Name', step_id, run_status,
LEFT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2) + ':' +
SUBSTRING(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),3,2) + ':' +
RIGHT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2) as 'Run Time',a.job_id
into #temp
from sysjobs a, sysjobhistory b
where run_status in (0,3)
and SUBSTRING(CAST(run_date AS CHAR(8)),5,2) + '/' +
RIGHT(CAST(run_date AS CHAR(8)),2) + '/' +
LEFT(CAST(run_date AS CHAR(8)),4) = CONVERT(varchar(10),getdate(),101)
and b.step_name not like '(job outcome)'
and a.job_id = b.job_id
order by a.name
select [job name], step_name, a.step_id, CASE a.run_status
WHEN 1 THEN 'Succeeded'
end as 'Status',
LEFT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2) + ':' +
SUBSTRING(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),3,2) + ':' +
RIGHT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2) as 'Run Time',a.job_id
from sysjobhistory a, #temp b
where a.run_status = 1
and SUBSTRING(CAST(run_date AS CHAR(8)),5,2) + '/' +
RIGHT(CAST(run_date AS CHAR(8)),2) + '/' +
LEFT(CAST(run_date AS CHAR(8)),4) = CONVERT(varchar(10),getdate(),101)
and [step name] = step_name
drop table #temp
GO 
