[作/译者]：鹏城万里 [日期]：2008-06-22 [来源]：本站原创 [查看]: 1816
【鹏城万里】 发表于 www.sqlstudy.com
查看 SQL Server 作业（job） 运行成功与否、还是取消等状态信息，以及作业最后一次运行持续时间、出错信息提示等。DBA 可以周期性地调度该 SQL 脚本，在 SQL Server 作业失败时候得到及时提醒。此 SQL 脚本适用于 SQL Server 2000 & 2005。
-------------------------------------------------------------------------------- -- author : p.c.w.l -- source : www.sqlstudy.com -- create : 2008-01-01 -- descr : a simple sql script to view sql server jobs run status -------------------------------------------------------------------------------- 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] */
本文由 www.sqlstudy.com 原创，版权所有，转载请注明作者和出处！
本文链接：http://www.sqlstudy.com/sql_article.php?id=2008061803
