2

I'm trying to figure out how SQL server groups their job history hierarchy to relate an outcome to all job steps. I'm using a data visualization tool to track errors in a dashboard, but the whole (Job outcome) step is driving me crazy.

Is there no ID links all the steps in a given job execution? I suppose that the instance_id + max(step_id) gives the instance_id of the step_id = 0 (the Job outcome). Given that the log file viewer has the results in an expandable hierarchy, I figure there must be some consistent way to represent that hierarchy in the database.

Using the job_id + run_date would work on jobs that only happen once a day where all steps are completed the same day, but when jobs run multiple times a day that breaks down.

Basically I want to join the job history table to itself where I have the job outcome for the job execution joined to each step so I could build a hierarchy like the one seen in the log viewer in my visualization tool.

Log viewer example image:

enter image description here

Any ideas?

I found the solution, the key is that the steps will be within the job start time and the job run duration:

`

select *
from
(
select sjh.run_date job_run_date
    , jobs.name job_name
    ,dbo.agent_datetime(sjh2.run_date,sjh2.run_time) job_start_time
    ,CASE sjh2.run_status WHEN 0 THEN 'Job Failed' WHEN 1 THEN 'Job Succeeded' END job_status
    ,sjh2.message job_message
    ,STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(sjh2.run_duration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') 'job_run_duration'
    , sjh.step_id
    , sjh.step_name
    ,dbo.agent_datetime(sjh.run_date,sjh.run_time) step_run_time
    ,CASE sjh.run_status WHEN 0 THEN 'Step Failed' WHEN 1 THEN 'Step Succeeded' END step_status
    ,sjh.message step_message
    ,STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(sjh.run_duration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') 'step_run_duration'
--  , *
from sysjobhistory sjh
join sysjobs jobs on jobs.job_id = sjh.job_id
join sysjobhistory sjh2 on sjh2.run_date = sjh.run_date 
                       and sjh2.job_id = sjh.job_id 
                       and sjh2.step_id = 0
                       and sjh.run_time >= sjh2.run_time
                       and sjh.run_time <= sjh2.run_time + sjh2.run_duration
) t1
order by job_run_date desc, job_name desc, job_start_time, step_id

`

henhen
  • 23
  • 9
  • Appears to be, ironically, the `job_history_id` field located in `msdb.dbo.sysjobactivity`. Not the `job_id` as that's the key for the entire job itself, which one would *think* would be located in `msdb.dbo.sysjobhistory`... MS definitely made this as non-user-friendly as possible here. It's also `NULL` for a lot of cases. Not sure how far you will get with this. – Jacob H Feb 06 '18 at 19:16
  • Thanks for the info! How bizarre. So I see `job_history_id` , which is great, except that it doesn't seem that `sysjobhistory` has that field on the steps! I feel like I'm going crazy, but that doesn't seem to make any sense. Don't people want to know which steps are associated with a given job run outside of the log viewer? – henhen Feb 06 '18 at 19:39
  • 2
    Okay, I figured out a way to build the hierarchy.. the trick is that the step run time will fall between the job start time and the job start time plus the duration (from the `step_id=0`) . I still think it's crazy that there is no key for a job execution, but hey....`` – henhen Feb 07 '18 at 01:05

0 Answers0