NOVOTS KMS 词汇表 Glossary    联系我们 Contact Us
查询 Search  
   
按类别浏览 Browse by Category
NOVOTS KMS .: 数据库 .: 利用视图查看作业的执行情况

利用视图查看作业的执行情况

-- 监控作业运行状况 
create view Job_status1
with encryption
as
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;
go


这篇文章对你多有用?

用户评语

添加评语
当前还没有评语.


.: .: .: .: .:
[ 登陆 ]
北京护航科技有限公司 2006

Novots Technologies Limited