SQL Server 作业信息和作业的调度控制

一、作业基本信息

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 

 

 

四、补充控制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