查看sql 作业明细及运行记录

最新推荐文章于 2022-09-20 10:36:24 发布

路庆华的前端博客

最新推荐文章于 2022-09-20 10:36:24 发布

阅读量1.3w

点赞数 1

文章标签： sql server 作业数据库

本文为博主原创文章，未经博主允许不得转载。

本文链接：https://blog.csdn.net/lqh4188/article/details/42968727

版权

--查看作业明细及状态
select j.name 'Job名',
       j.description '描述',
       j.ENABLED job_enabled,
       cast(js.last_run_date as varchar(10)) + '-' + cast(js.last_run_time as varchar(10)) last_run_time,
       js.last_run_duration last_run_duration,
       js.last_run_outcome last_run_status,
       last_run_msg = js.last_outcome_message + cast(nullif(js.last_run_outcome,1) as varchar(2)),
       j.date_created job_created,
       j.date_modified job_modified
  from msdb.dbo.sysjobs j
         inner join msdb.dbo.sysjobservers js
    on j.job_id = js.job_id
         inner join msdb.dbo.syscategories jc
    on j.category_id = jc.category_id
where j.enabled = 1
   and js.last_run_outcome in (0,1,3,5)      -- 0:Fail 1:Succ 3:Cancel 5:First run
   and jc.category_id not between 10 and 20  -- repl
   
--查看作业运行记录   
SELECT      [JobName]   = JOB.name,  
            [Step]      = HIST.step_id,  
            [StepName]  = HIST.step_name,  
            [Message]   = HIST.message,  
            [Status]    = CASE WHEN HIST.run_status = 0 THEN 'Failed'  
            WHEN HIST.run_status = 1 THEN 'Succeeded'  
            WHEN HIST.run_status = 2 THEN 'Retry'  
            WHEN HIST.run_status = 3 THEN 'Canceled'  
            END,  
            [RunDate]   = HIST.run_date,  
            [RunTime]   = HIST.run_time,  
            [Duration]  = HIST.run_duration  
FROM        msdb.dbo.sysjobs JOB  
INNER JOIN  msdb.dbo.sysjobhistory HIST ON HIST.job_id = JOB.job_id    
/* WHERE    JOB.name = 'Job_Name' */  
ORDER BY    HIST.run_date, HIST.run_time