select from sp_help_job

--查看job目前執行狀況

select 

 name

,last_run_date as lastdt

,last_run_time as lasttm
,case last_run_outcome
 when 0 then 'Failed.'
 when 1 then 'Succeeded.'
 when 3 then 'Canceled.'
 when 5 then 'Unknown'
 else 'others' end as lastOutcome
,case current_execution_status
 when 1 then 'Executing.'
 when 2 then 'Waiting for thread.'
 when 3 then 'Between retries.'
 when 4 then 'Idle.'
 when 5 then 'Suspended'
 when 7 then 'Performing completion actions.'
 else 'others.' end as currentStatus
from openrowset ('SQLNCLI', 'Database=msdb;Uid=sa;Pwd=xxxx;','set fmtonly off;exec sp_help_job')
where name like '%yourJobName%'




參考database journal 文章Detecting The State of a SQL Server Agent Job的欄位說明