检查SQL Server Job状态

exec msdb.dbo.sp_help_job @execution_status=1

select distinct j.Name as "Job Name", j.description as "Job Description", h.run_date as LastStatusDate, h.run_time as LastStatusTime, h.run_duration as LastStatusDuration, case h.run_status when 0 then 'Failed' when 1 then 'Successful' when 3 then 'Cancelled' when 4 then 'In Progress' end as JobStatus from msdb..sysJobHistory h, msdb..sysJobs j where j.job_id = h.job_id and h.step_id = 1 and h.run_date = (select max(hi.run_date) from msdb..sysJobHistory hi where h.job_id = hi.job_id) and h.run_time = (select max(hj.run_time) from msdb..sysJobHistory hj where h.job_id = hj.job_id) order by 1

select distinct cat.name as "Category", j.Name as "Job Name", j.description as "Job Description", h.run_date as LastStatusDate, h.run_time as LastStatusTime, h.run_duration as LastStatusDuration, case h.run_status when 0 then 'Failed' when 1 then 'Successful' when 3 then 'Cancelled' when 4 then 'In Progress' end as JobStatus from msdb..sysJobHistory h, msdb..sysJobs j, msdb..syscategories cat where j.job_id = h.job_id and j.category_id = cat.category_id and h.step_id = 1 and h.run_date = (select max(hi.run_date) from msdb..sysJobHistory hi where h.job_id = hi.job_id) and h.run_time = (select max(hj.run_time) from msdb..sysJobHistory hj where h.job_id = hj.job_id) order by 1,3

Select [Job Name] = j.name , [Job Description] = j.description , [LastRunDate] = h.run_date , [LastRunTime] = h.run_time , [JobStatus] = Case h.run_status When 0 Then 'Failed' When 1 Then 'Successful' When 3 Then 'Cancelled' When 4 Then 'In Progress' End ,[OrderOfRun] = Rank() Over(Partition By j.job_id Order By h.run_date Desc,h.run_time Desc) From msdb.dbo.sysjobhistory h Left join msdb.dbo.sysjobs j On j.job_id = h.job_id Where h.step_id=0 --only look @ Job Outcome step Order By [Job Name] desc,Rank() Over(Partition By j.job_id Order By h.run_date Desc,h.run_time Desc) Asc