query that shows run start and duration on sql agent history

db042190 1,516 Reputation points

hi we run 2019 enterprise and could use an historic query that not only tells us when a certain job runs 10 minutes or longer but how often 2 of its steps run past the hr and half hr. so lets say the job is called J. not only is J interesting to us when it starts running before the hr and is till running past the hr or starts before the half hr and is still running past the half hr, but its 6th and 7th steps are also of interest to us. namely, in those situations i just described, how often are those steps at least done or not done before the hr or half hr. and durations of course (job, steps) would be interesting to us too.

0 comments No comments
{count} votes

Accepted answer
  1. Jonathon Kindred 401 Reputation points

    Basically this will give you the initial dataset you're after:

     j.name as 'JobName',  
     msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',  
    From msdb.dbo.sysjobs j   
    INNER JOIN msdb.dbo.sysjobhistory h   
     ON j.job_id = h.job_id   
    where j.enabled = 1  --Only Enabled Jobs  
    order by JobName, RunDateTime desc  

    You'll have to do a CTE on that to get the info you need.

3 additional answers

Sort by: Most helpful
  1. db042190 1,516 Reputation points

    i have this so far. already wondering what run_duration on the job history step is. it always seems to be in whole numbers whatever it is. and so far i dont see an instance that ties the steps to a specific run of the job. but am looking further.

    select sj.name,s.*   
    from sysjobhistory s  
    join sysjobs sj  
    on sj.job_id=s.job_id  
    where name like '...%'  
    0 comments No comments

  2. db042190 1,516 Reputation points

    i do see a table called sysjobactivity. it has a history and session id. unfortunately, unless i'm missing something, the only way i can see to join the step based table to it is by its start and end times and job id. i'm guessing for the moment that the duration i mentioned earlier is seconds.

    0 comments No comments

  3. db042190 1,516 Reputation points

    thx jonathan, i'm having trouble typing it to a specific run/instance of the job. i'm using this query and dont believe its working. the start and stop time of the job itself will be important. sorry about the repetitive substring but i think you get the idea. my assumption right now is that sysjobhistory is at the step level while sysjobactivity is at the job instance level.

    select sj.name,sja.job_history_id,sja.session_id,s.*   
    from sysjobhistory s  
    join sysjobs sj  
    on sj.job_id=s.job_id  
    join sysjobactivity sja  
    on s.job_id=sja.job_id and  
       --CONVERT(DATETIME, CONVERT(CHAR(8), s.run_date, 112)  
       cast(CONVERT(date, CONVERT(varchar(8), s.run_date)) as varchar(10))  
      + ' ' + --CONVERT(CHAR(8), s.run_time, 108))  
           substring(RIGHT('000000' + CAST(s.run_Time AS VARCHAR), 6),1,2) + ':' +   
    	   substring(RIGHT('000000' + CAST(s.run_Time AS VARCHAR), 6),3,2) + ':' +   
    	   substring(RIGHT('000000' + CAST(s.run_Time AS VARCHAR), 6),5,2)  
    	   as datetime)  
          between sja.start_execution_date and sja.stop_execution_date  
    where name like '...%'