在SQL Server中判斷一個作業是否正在運作

select step_id,run_status,run_date As rundate

--a.run_time, a.*

from msdb.dbo.sysjobhistory a

inner join msdb.dbo.sysjobs b on a.job_id=b.job_id

where b.name='PCDBI'

order by run_date DESC,step_id Asc

作業的執行狀态run_status：

0 = 失敗

1 = 成功

2 = 重試

3 = 取消

4 = 正在進行

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

-- author : p.c.w.l

-- source : www.sqlstudy.com

-- create : 2008-01-01

-- descr  : a simple sql script to view sql server jobs run status

--------------------------------------------------------------------------------

select category          = jc.name,

       category_id       = jc.category_id,

       job_name          = j.name,

       job_enabled       = j.enabled,

       last_run_time     = cast(js.last_run_date as varchar(10)) + '-' + cast(js.last_run_time as varchar(10)),

       last_run_duration = js.last_run_duration,

       last_run_status   = js.last_run_outcome,

       last_run_msg      = js.last_outcome_message + cast(nullif(js.last_run_outcome,1) as varchar(2)),

       job_created       = j.date_created,

       job_modified      = j.date_modified

  from msdb.dbo.sysjobs j

         inner join msdb.dbo.sysjobservers js

    on j.job_id = js.job_id

         inner join msdb.dbo.syscategories jc

    on j.category_id = jc.category_id

 where j.enabled = 1

   and js.last_run_outcome in (0,1,3,5)      -- 0:Fail 1:Succ 3:Cancel 5:First run

   and jc.category_id not between 10 and 20  -- repl

轉載于:https://www.cnblogs.com/webcc/archive/2012/08/15/2523712.html