查看 SQL Server 作业(job)运行结果状态脚本

查看 SQL Server 作业（job） 运行成功与否、还是取消等状态信息，以及作业最后一次运行持续时间、出错信息提示等。DBA 可以周期性地调度该 SQL 脚本，在 SQL Server 作业失败时候得到及时提醒

select category          = jc.name,
       category_id       = jc.category_id,
       job_name          = j.name,
       job_enabled       = j.enabled,
       last_run_time     = cast(js.last_run_date as varchar(10)) + '-' + cast(js.last_run_time as varchar(10)),
       last_run_duration = js.last_run_duration,
       last_run_status   = js.last_run_outcome,
       last_run_msg      = js.last_outcome_message + cast(nullif(js.last_run_outcome,1) as varchar(2)),
       job_created       = j.date_created,
       job_modified      = j.date_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


/*
   category_id name
   0  [Uncategorized (Local)]
   1  Jobs from MSX
   2  [Uncategorized (Multi-Server)]
   3  Database Maintenance
   4  Web Assistant
   5  Full-Text
   6  Log Shipping
   7  Database Engine Tuning Advisor
   10 REPL-Distribution
   11 REPL-Distribution Cleanup
   12 REPL-History Cleanup
   13 REPL-LogReader
   14 REPL-Merge
   15 REPL-Snapshot
   16 REPL-Checkup
   17 REPL-Subscription Cleanup
   18 REPL-Alert Response
   19 REPL-QueueReader
   20 Replication
   98 [Uncategorized]
   99 [Uncategorized]
*/