Vilket jobb har kört längst, kortast och medel tid

Med det här scriptet får man upp tider på jobb som finns i jobbhistoriken. Tänk på om det rensats strax innan scriptet exekveras så ger det ingen rättvis bild.

;WITH JobHistory AS
(
    SELECT
        j.name AS JobName,
        msdb.dbo.agent_datetime(h.run_date, h.run_time) AS RunDate,
    -- SQL Agent lagrar duration som HHMMSS
    (h.run_duration / 10000) * 3600
    + ((h.run_duration % 10000) / 100) * 60
    + (h.run_duration % 100) AS DurationSeconds

FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j
    ON h.job_id = j.job_id
WHERE h.step_id = 0
  AND h.run_status = 1       -- Endast lyckade körningar
),
Stats AS
(
SELECT
JobName,
COUNT(*) AS NumberOfRuns,
MIN(RunDate) AS OldestHistory,
MAX(RunDate) AS LatestRun,
MIN(DurationSeconds) AS MinSeconds,
AVG(CAST(DurationSeconds AS BIGINT)) AS AvgSeconds,
MAX(DurationSeconds) AS MaxSeconds
FROM JobHistory
GROUP BY JobName
)
SELECT
JobName,
NumberOfRuns,
OldestHistory,
LatestRun,
FORMAT(MinSeconds / 3600, '00') + ':' +
FORMAT((MinSeconds % 3600) / 60, '00') + ':' +
FORMAT(MinSeconds % 60, '00') AS Fastest,

FORMAT(AvgSeconds / 3600, '00') + ':' +
FORMAT((AvgSeconds % 3600) / 60, '00') + ':' +
FORMAT(AvgSeconds % 60, '00') AS Average,

FORMAT(MaxSeconds / 3600, '00') + ':' +
FORMAT((MaxSeconds % 3600) / 60, '00') + ':' +
FORMAT(MaxSeconds % 60, '00') AS Longest
FROM Stats
ORDER BY MaxSeconds DESC;