检查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 jobstatusfrom msdb..sysjobhistory h, msdb..sysjobs jwhere j.job_id = h.job_idand h.step_id = 1and 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 jobstatusfrom msdb..sysjobhistory h, msdb..sysjobs j, msdb..syscategories catwhere j.job_id = h.job_id andj.category_id = cat.category_idand h.step_id = 1and 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 steporder by [job name] desc,rank() over(partition by j.job_id order by h.run_date desc,h.run_time desc) asc