Sql-server – TSQL query for viewing start/end of all SQL Agent jobs yesterday

sql serversql-server-2012

See this related query: What's a good tool for visualizing SQL Agent jobs?

I'm not after a visual tool however, since I am looking to build my own visualisation. This is my starting point, but you'll agree it's sorely lacking.

select TOP(1000)
       h.run_date, min(h.run_time), j.name, j.description, max(h.run_duration) run_duration
from msdb..sysjobhistory h
join msdb..sysjobs j on j.job_id = h.job_id
group by h.run_date, j.name, j.description, h.instance_id, j.job_id
having max(h.run_duration) > 1000  -- << -- 10 minutes encoded in HHMMSS (digits)
order by h.run_date desc, 2 desc

The crux is that there does not seem to be a key to the sysjobhistory table to identify it as a particular "instance" of a sysjob. For example, a job that started at 12:30 with 4 steps gets inserted into sysjobhistory 4 times with different instance_ids (unique). They all tie to the sysjob.job_id value, but that's the schedule, not the invocation of the job. Is there another table in which I should look?

This is probably fairly simple, since SSMS is able to do it easily.

Best Answer

You just need to filter where the step_id = 0 which is the job outcome and has the total run duration.

SELECT TOP ( 1000 )
        h.run_date ,
        MIN(h.run_time) ,
        j.name ,
        j.description ,
        MAX(h.run_duration) run_duration
FROM    msdb..sysjobhistory h
        JOIN msdb..sysjobs j ON j.job_id = h.job_id
WHERE   step_id = 0
GROUP BY h.run_date ,
        j.name ,
        j.description ,
        h.instance_id ,
        j.job_id
HAVING  MAX(h.run_duration) > 1000  -- << -- 10 minutes encoded in HHMMSS (digits)
ORDER BY h.run_date DESC ,
        2 DESC
Related Question