How to check the MS SQL Server job run status by using Script

SELECT  a.name as job, left(b.step_name, 20) as [step name],

CASE b.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' ELSE

'Running'  END as status,

SUBSTRING(CAST(b.run_date AS CHAR(8)),5,2) + '/' + RIGHT(CAST(b.run_date AS CHAR(8)),2) + '/' + LEFT(CAST

(b.run_date AS CHAR(8)),4) as [date],

LEFT(RIGHT('000000' + CAST(b.run_time AS VARCHAR(10)),6),2) + ':' +  SUBSTRING(RIGHT('000000' + CAST(b.run_time AS

VARCHAR(10)),6),3,2) + ':' +  RIGHT(RIGHT('000000' +  CAST(b.run_time AS VARCHAR(10)),6),2) as [time],

b.message as Err_message

From msdb..sysjobs a

INNER JOIN msdb..sysjobhistory b ON a.job_id = b.job_id

inner join (select job_id,max(instance_id)  as maxinstance from msdb..sysjobhistory  group by job_id) x

on a.job_id = x.job_id and b.instance_id = x.maxinstance  --b.instance_id = x.maxinstance--最近一次运行结果

where a.enabled =1 and b.run_status <>1 and b.run_status <> 4 --查看运行失败以及异常取消的

ORDER BY job, convert(char, b.run_date,111)+convert(char,b.run_time,111)  desc

BTW

For the run_status column of sysjobhistory, msdn gives the following statuses that can be returned:

Status of the job execution:

0 = Failed

1 = Succeeded

2 = Retry

3 = Canceled

4 = In progress