一、作业基本信息
select a.name 'Job名称',a.[description] 作业描述,
作业创建时间=a.date_created,
上次修改作业时间=a.date_modified,
下一次运行日期=substring(ltrim(b.next_run_date),1,4)+'-' + substring(ltrim(b.next_run_date),5,2)+'-' + right(ltrim(b.next_run_date),2),
下一次运行时间=substring(right('000000'+convert(varchar,b.next_run_time),6),1,2)+':' + substring(right('000000'+convert(varchar,b.next_run_time),6),3,2) +':' + + substring(right('000000'+convert(varchar,b.next_run_time),6),5,2),
case when (c.freq_type=4 and c.freq_subday_type=4) then ('每'+convert(varchar,c.freq_subday_interval)+'分钟')
when (c.freq_type=4 and c.freq_subday_type=8) then ('每'+convert(varchar,c.freq_subday_interval)+'小时')
when (c.freq_type=4 and c.freq_subday_type=1) then ('每天一次:' + substring(right('000000'+convert(varchar,active_start_time),6),1,2)+':' + substring(right('000000'+convert(varchar,active_start_time),6),3,2) +':' + + substring(right('000000'+convert(varchar,active_start_time),6),5,2) )
when (c.freq_type=8 and c.freq_interval=1 and c.freq_subday_type=1) then ('每星期天:' + right('000000'+convert(varchar,active_start_time),6) )
when (c.freq_type=8 and c.freq_interval=2 and c.freq_subday_type=1) then ('每星期一:' + right('000000'+convert(varchar,active_start_time),6) )
when (c.freq_type=8 and c.freq_interval=4 and c.freq_subday_type=1) then ('每星期二:' + right('000000'+convert(varchar,active_start_time),6) )
when (c.freq_type=8 and c.freq_interval=8 and c.freq_subday_type=1) then ('每星期三:' + right('000000'+convert(varchar,active_start_time),6) )
when (c.freq_type=8 and c.freq_interval=16 and c.freq_subday_type=1) then ('每星期四:' + right('000000'+convert(varchar,active_start_time),6) )
when (c.freq_type=8 and c.freq_interval=32 and c.freq_subday_type=1) then ('每星期五:' + right('000000'+convert(varchar,active_start_time),6) )
when (c.freq_type=8 and c.freq_interval=64 and c.freq_subday_type=1) then ('每星期六:' + right('000000'+convert(varchar,active_start_time),6) )
when (c.freq_type=8 and c.freq_interval=1 and c.freq_subday_type=8) then ('每星期天:每' +convert(varchar,c.freq_subday_interval)+'小时')
else '未知' end +' 开始执行' '频率',
case when c.active_end_date=99991231 then '永久' else convert(varchar,c.active_end_date) end '期限'
from msdb.dbo.sysjobs a with(nolock) inner join msdb.dbo.sysjobschedules b with(nolock) on a.job_id = b.job_id
inner join msdb.dbo.sysschedules c with(nolock) on b.schedule_id=c.schedule_id
where a.[enabled]=1 and c.[enabled]=1 order by 1;
二、作业执行情况统计
SELECT 作业的名称 = name,
对作业的说明 = description,
计划运行作业的下一个日期 = (SELECT top 1 left(left(next_run_date,4)+'-'+right(next_run_date,4),7)+'-'+right(next_run_date,2)
FROM msdb.dbo.sysjobschedules
WHERE job_id = sysjobs.job_id),
计划运行作业的时间 = (SELECT top 1 left(left(left('000000',6-len(next_run_time))+ltrim(next_run_time),2)+':'+right(left('000000',6-len(next_run_time))+ltrim(next_run_time),4),5)+':'+right(left('000000',6-len(next_run_time))+ltrim(next_run_time),2)
FROM msdb.dbo.sysjobschedules
WHERE job_id = sysjobs.job_id),
作业的执行状态 = CASE (SELECT top 1 run_status
FROM msdb.dbo.sysjobhistory
WHERE job_id = sysjobs.job_id
ORDER BY instance_id DESC)
WHEN 0 THEN '失败'
WHEN 1 THEN '成功'
WHEN 2 THEN '重试'
WHEN 3 THEN '已取消'
WHEN 4 THEN '正在进行中'
END,
作业或步骤开始执行的日期 = (SELECT top 1 left(left(run_date,4)+'-'+right(run_date,4),7)+'-'+right(run_date,2)
FROM msdb.dbo.sysjobhistory
WHERE job_id = sysjobs.job_id
ORDER BY instance_id DESC),
作业或步骤开始的时间 = (SELECT top 1 left(left(left('000000',6-len(run_time))+ltrim(run_time),2)+':'+right(left('000000',6-len(run_time))+ltrim(run_time),4),5)+':'+right(left('000000',6-len(run_time))+ltrim(run_time),2)
FROM msdb.dbo.sysjobhistory
WHERE job_id = sysjobs.job_id
ORDER BY instance_id DESC),
执行作业或步骤所花费的时间 = (SELECT top 1 left(left(left('000000',6-len(run_duration))+ltrim(run_duration),2)+'小时'+right(left('000000',6-len(run_duration))+ltrim(run_duration),4),5)+'分钟'+right(left('000000',6-len(run_duration))+ltrim(run_duration),2)+'秒'
FROM msdb.dbo.sysjobhistory
WHERE job_id = sysjobs.job_id
ORDER BY instance_id DESC)
FROM msdb.dbo.sysjobs
三、某一个作业的详情(如:NOAS.DW 的20120411 执行情况)
select 作业名称=tb.name,步骤=ta.step_name,
错误的严重级别=ta.sql_severity,
消息=ta.message,
执行状态=case when run_status=0 then '失败'
when run_status=1 then '成功'
when run_status=2 then '重试'
when run_status=3 then '已取消' end,
重试次数=retries_attempted,
步骤顺序=ta.step_id,
花费的时间=substring(right('000000'+ltrim(ta.run_duration),6),1,2)+':'+SUBSTRING(right('000000'+ltrim(ta.run_duration),6),3,2)+':'+RIGHT(right('000000'+ltrim(ta.run_duration),6),2)
from dbo.sysjobhistory ta,sysjobs tb
where ta.job_id=tb.job_id and tb.name='NOAS.DW'
and run_date='20120411'
order by step_id
![](https://img-my.csdn.net/uploads/201204/11/1334135432_2954.jpg)
四、补充控制JOB的作业
1、重新执行作业:
USE msdb ;
GO
EXEC dbo.sp_start_job N'作业名称' ;
GO
2、取消执行作业:
USE msdb ;
GO
EXEC dbo.sp_stop_job N'作业名称' ;
GO
3、禁用作业:
USE msdb ;
GO
EXEC dbo.sp_update_job
@job_name = N'作业名称',
@new_name = N'作业名称-A',
@description = N'描述',
@enabled = 0 ;
GO
4、更改作业调度时间
EXEC dbo.sp_update_schedule @name = 'Ods.Eoms',@active_start_time = 0815