SQL Server作业信息和作业的调度控制简析(2)
来源：未知 责任编辑：责任编辑 发表时间:2013-11-26 22:15 点击:次
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;
二、作业执行情况统计
[sql]
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) www.2cto.com
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)
相关新闻>>
最新推荐更多>>>
- 发表评论
-
- 最新评论 更多>>
