In SQL Server 2008 R2, there is this sysjobhistory table. If a job has several steps, they are all listed there, in the order they were executed/completed. So if no jobs run in parallel, if everything is executed sequentially, I can easily see all the steps that belong to one execution of the job using instance_id (descending order) and step_id:
instance_id job_id step_id step_name run_status
-------------------------------------------------------
15 aaa 0 (Job outcome) 1
14 aaa 3 step a3 1
13 aaa 2 step a2 1
12 aaa 1 step a1 1
11 bbb 0 (Job outcome) 1
10 bbb 2 step b1 1
9 bbb 1 step b2 1
8 aaa 0 (Job outcome) 1
7 aaa 3 step a3 1
6 aaa 2 step a2 1
5 aaa 1 step a1 1
Here, I can easly create a query to show a job and the steps for it in order of execution. So with the data above, what I want to achieve is a result like this (I can do that using a cursor and a temp table to create my own sort order):
instance_id job_id step_id step_name run_status
-------------------------------------------------------
15 aaa 0 (Job outcome) 1
12 aaa 1 step a1 1
13 aaa 2 step a2 1
14 aaa 3 step a3 1
11 bbb 0 (Job outcome) 1
9 bbb 1 step b1 1
10 bbb 2 step b2 1
8 aaa 0 (Job outcome) 1
5 aaa 1 step a1 1
6 aaa 2 step a2 1
7 aaa 3 step a3 1
Outcome first and in order of execution date/time, then each step for that particular job in ascending order.
However, if one job still runs while a different job starts, it's not that easy any more:
instance_id job_id step_id step_name run_status
-------------------------------------------------------
31 aaa 0 (Job outcome) 1
30 aaa 1 step a1 1
29 bbb 0 (Job outcome) 1
28 bbb 1 step b1 1
27 bbb 2 step b2 1
26 aaa 2 step a2 1
25 aaa 3 step a3 1
How can I know that e.g. instance_ids 31, 30, 26, and 25 belong to the same execution of job aaa? I can view a log file in SSMS which shows me waht I want - the jobs itself (records with step_id 0) order by execution date and when I expand a job, all steps for it. So the information which history records belong to a single execution of a job is there somehow. How can I get that information?