6

The following is a script I found online that has most of the elements of what I desire in the result set. The only thing missing is how I can get an average of the run times over a decided time period, like a week, 10 days, 23 days, a month, etc.

I've done some searching, but what I've tried has failed in some way or another. Further, my TSQL are weak, but I'm reading publications by Itzik Ben-gan to change this matter. I'd truly appreciate any help that anyone can provide.

select job_name, run_datetime, run_duration
from
(
    select job_name, run_datetime,
        SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
        SUBSTRING(run_duration, 5, 2) AS run_duration
    from
    (
        select DISTINCT
            j.name as job_name, 
            run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +  
                (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
            run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
        from msdb..sysjobhistory h
        inner join msdb..sysjobs j
        on h.job_id = j.job_id
    ) t
) t
order by job_name, run_datetime
7

Updated to satisfy the new requirement. Note that you may be tempted to use CONVERT(TIME, but resist - this is brittle, in the event any job runs longer than 24 hours.

SELECT 
  job_name = name, 
  avg_sec  = rd,
  avg_hhmm = CONVERT(VARCHAR(11),rd / 60) + ':' + RIGHT('0' + CONVERT(VARCHAR(11),rd % 60), 2)
FROM
(
  SELECT
    j.name, 
    rd = AVG(DATEDIFF(SECOND, 0, STUFF(STUFF(RIGHT('000000' 
       + CONVERT(VARCHAR(6),run_duration),6),5,0,':'),3,0,':')))
  FROM msdb.dbo.sysjobhistory AS h
  INNER JOIN msdb.dbo.sysjobs AS j
  ON h.job_id = j.job_id
  WHERE h.step_id = 0
  GROUP BY j.name
) AS t
ORDER BY job_name;
| improve this answer | |
  • I don't believe this is logically correct. The history table contains a 0 (zero) step with the step_name of (job outcome) This row contains the total run duration of all the steps in the job. You just need to average these rows. Subquery should look like this: – AWEInCA Jan 19 '16 at 18:56
  • SELECT j.name, rd = AVG(DATEDIFF(SECOND, 0, STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(6),run_duration),6),5,0,':'),3,0,':'))) FROM msdb.dbo.sysjobhistory AS h INNER JOIN msdb.dbo.sysjobs AS j ON h.job_id = j.job_id AND h.step_id = 0 GROUP BY j.name – AWEInCA Jan 19 '16 at 19:01
  • @AWEInCA I think you're onto something, however one problem with relying on step 0 is that you have to wait for a job to finish before collecting its duration. If this is to be used as an early indicator for a job that is currently running longer than it should (or at least longer than the average), you'll still want to look outside of step 0. – Aaron Bertrand Jan 19 '16 at 19:06
  • This is the exact query I'm actually looking for. Thanks Aaron. – Sunil Kumar Jun 23 '16 at 5:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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