8

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
2

2 Answers 2

10

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_mmss = 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;
5
  • 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
    Commented Jan 19, 2016 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
    Commented Jan 19, 2016 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. Commented Jan 19, 2016 at 19:06
  • This is the exact query I'm actually looking for. Thanks Aaron. Commented Jun 23, 2016 at 5:07
  • 1
    This is very helpful, thanks Aaron! One thing to point out, the 3rd column is returning "avg_mmss" instead of "avg_hhmm".
    – BateTech
    Commented Aug 2, 2021 at 16:41
-1

Updated Script

DROP TABLE IF EXISTS #Temp;

SELECT
 h.job_id,
 msdb.dbo.agent_datetime(run_date,run_time) as RunDate,
 CONVERT(int, SUBSTRING(RIGHT(REPLICATE('0', 6) +  CAST(run_duration as varchar(6)), 6),5,2))
 + CONVERT(int,SUBSTRING(RIGHT(REPLICATE('0', 6) +  CAST(run_duration as varchar(6)), 6),3,2)) * 60
 + CONVERT(int, SUBSTRING(RIGHT(REPLICATE('0', 6) +  CAST(run_duration as varchar(6)), 6),1,2)) * 60 * 60 As TotalRunSeconds 
into #Temp
FROM msdb.dbo.sysjobhistory h
Where step_id = 0;

SELECT
  job_name = name,
  avg_sec  = rd,
  avg_mmss = CONVERT(VARCHAR(11),rd / 60) + ':' + RIGHT('0' + CONVERT(VARCHAR(11),rd % 60), 2)
FROM
(
  SELECT
    j.name,
    rd = AVG(
        DATEDIFF(SECOND, RunDate, DATEADD(SECOND, TotalRunSeconds, RunDate)))
  FROM #Temp AS h
  INNER JOIN msdb.dbo.sysjobs AS j
  ON h.job_id = j.job_id where j.name='Maintenance - Backups - USER - FULL'
  GROUP BY j.name
) AS t
ORDER BY job_name;

DROP TABLE IF EXISTS #Temp;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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