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' */
