SQL Server作业信息和作业的调度控制简析(二)
dules
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 执行情况)
[sql]
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, www.2cto.com
花费的时间=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的作业
[sql]
1、重新执行作业：
USE msdb ;
GO
EXEC dbo.sp_start_job N'作业名称' ;
GO
[sql]
2、取消执行作业：
USE msdb ;
GO
EXEC dbo.sp_stop_job N'作业名称' ;
GO
[sql]
www.2cto.com
3、禁用作业：
USE msdb ;
GO
EXEC dbo.sp_update_job
@job_name = N'作业名称',
@new_name = N'作业名称-A',
@description = N'描述',
@enabled = 0 ;
GO
[sql]
4、更改作业调度时间
EXEC dbo.sp_update_schedule @name = 'Ods.Eoms',@active_start_time = 0815
作者 zlp321002