Sort order for job steps in SQL Server 2008 R2

337 views Asked by At

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?

0

There are 0 answers