query that shows run start and duration on sql agent history

Question

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.

Answer 1

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

select   
 j.name as 'JobName',  
 run_date,  
 run_time,  
 msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',  
 run_duration  
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.

Answer 2

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 '...%'  

Answer 3

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.

Answer 4

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(  
   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 '...%'