Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.