xp_sqlagent_enum_jobs 参数： xp_sqlagent_enum_jobs <is sysadmin (0 or 1)>, <job owner name> [, <job id>]備註：@is sysadmin=1@job owner name 可以随便给个字符串值 @job id 可有可无。例如，可以这样查询所有的作业状况： execute master.dbo.xp_sqlagent_enum_jobs 1, 'sa'查询特定 Job 的运行状态：declare @job_id uniqueidentifier select @job_id = job_id from msdb.dbo.sysjobs where name = 'job name'exec xp_sqlagent_enum_jobs 1, 'sa', @job_id判断 SQL Server 作业（Job）是否正在运行的代码。 1. create temp table to save jobs status create table #job_run_status ( job_id uniqueidentifier not null, last_run_date int not null, last_run_time int not null, next_run_date int not null, next_run_time int not null, next_run_schedule_id int not null, requested_to_run int not null, -- bool request_source int not null, request_source_id sysname collate database_default null, running int not null, -- bool current_step int not null, current_retry_attempt int not null, job_state int not null )2. get jobs status insert into #job_run_status execute master.dbo.xp_sqlagent_enum_jobs 1, 'sa'3. get running jobs select job_name = j.name ,s.* from #job_run_status s inner join msdb.dbo.sysjobs j on s.job_id = j.job_id where s.running = 1 -- running = 1master.dbo.xp_sqlagent_enum_jobs 非常有用，根据它返回的结果集，我们可以知道：一个 SQL Job 是否正在运行，Job 最后一次运行的时间，下次将要开始运行的时间，当前执行的是 Job 的第几步，以及重试了几次。__________________________________________________________________一 、查看Job是否在運行 Declare @Job_ID as UNIQUEIDENTIFIER select @Job_ID =Job_ID from msdb.dbo.sysjobs where name = 'PCDBI' Exec master..sp_MSget_jobstate @Job_ID 返回值为 1 - 正在运行 4 - 表示完成(成功或失败)二、查看執行的结果状态-- 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 三 、查看历史情况 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 = 正在进行本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/yoyoch1/archive/2009/05/20/4202995.aspx
xp_sqlagent_enum_jobs <is sysadmin (0 or 1)>, <job owner name> [, <job id>]備註：@is sysadmin=1@job owner name 可以随便给个字符串值 @job id 可有可无。例如，可以这样查询所有的作业状况： execute master.dbo.xp_sqlagent_enum_jobs 1, 'sa'查询特定 Job 的运行状态：declare @job_id uniqueidentifier
select @job_id = job_id from msdb.dbo.sysjobs where name = 'job name'exec xp_sqlagent_enum_jobs 1, 'sa', @job_id判断 SQL Server 作业（Job）是否正在运行的代码。
1. create temp table to save jobs status
create table #job_run_status
(
job_id uniqueidentifier not null,
last_run_date int not null,
last_run_time int not null,
next_run_date int not null,
next_run_time int not null,
next_run_schedule_id int not null,
requested_to_run int not null, -- bool
request_source int not null,
request_source_id sysname collate database_default null,
running int not null, -- bool
current_step int not null,
current_retry_attempt int not null,
job_state int not null
)2. get jobs status
insert into #job_run_status
execute master.dbo.xp_sqlagent_enum_jobs 1, 'sa'3. get running jobs
select job_name = j.name
,s.*
from #job_run_status s
inner join msdb.dbo.sysjobs j
on s.job_id = j.job_id
where s.running = 1 -- running = 1master.dbo.xp_sqlagent_enum_jobs 非常有用，根据它返回的结果集，我们可以知道：一个 SQL Job 是否正在运行，Job 最后一次运行的时间，下次将要开始运行的时间，当前执行的是 Job 的第几步，以及重试了几次。__________________________________________________________________一 、查看Job是否在運行
Declare @Job_ID as UNIQUEIDENTIFIER
select @Job_ID =Job_ID from msdb.dbo.sysjobs where name = 'PCDBI'
Exec master..sp_MSget_jobstate @Job_ID 返回值为 1 - 正在运行
4 - 表示完成(成功或失败)二、查看執行的结果状态-- 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 三 、查看历史情况
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 = 正在进行本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/yoyoch1/archive/2009/05/20/4202995.aspx