Declare @Job_ID as UNIQUEIDENTIFIER
select @Job_ID =Job_ID from msdb.dbo.sysjobs where name = 'James_Test'
Exec master..sp_MSget_jobstate @Job_ID

返回值为 1 - 正在运行  4 - 表示完成(成功或失败)

-- author : p.c.w.l
-- source :
-- create : 2008-11-27
-- 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


/*
   category_id name
   0  [Uncategorized (Local)]
   1  Jobs from MSX
   2  [Uncategorized (Multi-Server)]
   3  Database Maintenance
   4  Web Assistant
   5  Full-Text
   6  Log Shipping
   7  Database Engine Tuning Advisor
   10 REPL-Distribution
   11 REPL-Distribution Cleanup
   12 REPL-History Cleanup
   13 REPL-LogReader
   14 REPL-Merge
   15 REPL-Snapshot
   16 REPL-Checkup
   17 REPL-Subscription Cleanup
   18 REPL-Alert Response
   19 REPL-QueueReader
   20 Replication
   98 [Uncategorized]
   99 [Uncategorized]
*/

select run_status,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='作业名'