Select step_id, run_status, run_date as rundate
-- A. run_time, .*
From MSDB. DBO. sysjobhistory
Inner join MSDB. DBO. sysjobs B on A. job_id = B. job_id
Where B. Name = 'pcdbi'
Order by run_date DESC, step_id ASC
Job execution status run_status:
0 = failed
1 = successful
2 = retry
3 = Cancel
4 = in progress
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-- 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-subpartition cleanup
18 repl-Alert Response
19 repl-queuereader
20 Replication
98 [uncategorized]
99 [uncategorized]
*/