SQL Server Agent jobs are the backbone of automated database maintenance: backups, index rebuilds, integrity checks, ETL pipelines. Knowing at a glance when each job last ran and how long it took is essential for fast daily monitoring, especially when managing multiple instances. This query reads directly from the Agent system tables in msdb and returns the last execution time and duration for every job.

How It Works

The query joins msdb..sysjobs (job definitions) with msdb..sysjobhistory (execution history), finds the most recent run for each job, and formats the duration from SQL Server’s internal integer format (HHMMSS packed as an integer) into a human-readable HH:MM:SS string. It also returns the duration broken out in minutes and seconds for easy filtering.

SELECT
    job_id,
    job_name,
    run_datetime,
    SUBSTRING(run_duration, 1, 2) + ':' +
    SUBSTRING(run_duration, 3, 2) + ':' +
    SUBSTRING(run_duration, 5, 2)                           AS run_duration_hms,
    CONVERT(INT, SUBSTRING(run_duration, 1, 2)) * 60 +
    CONVERT(INT, SUBSTRING(run_duration, 3, 2))             AS duration_min,
    CONVERT(FLOAT, SUBSTRING(run_duration, 5, 2))           AS duration_sec
FROM
(
    SELECT
        j.job_id,
        j.name AS job_name,
        DATEADD(hh, -7, run_datetime) AS run_datetime,
        run_duration = RIGHT('000000' + CONVERT(VARCHAR(6), h.run_duration), 6)
    FROM
    (
        SELECT
            j.name AS job_name,
            run_datetime = MAX(
                CONVERT(DATETIME, RTRIM(run_date)) +
                (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4
            )
        FROM msdb..sysjobhistory h
        INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id
        GROUP BY j.name
    ) t
    INNER JOIN msdb..sysjobs j        ON t.job_name = j.name
    INNER JOIN msdb..sysjobhistory h
        ON  j.job_id = h.job_id
        AND t.run_datetime = CONVERT(DATETIME, RTRIM(h.run_date)) +
            (h.run_time * 9 + h.run_time % 10000 * 6 + h.run_time % 100 * 10) / 216e4
) dt
-- Uncomment to filter by a specific job:
-- WHERE job_id = @job_id
ORDER BY run_datetime DESC;

The DATEADD(hh, -7, ...) offset adjusts for a UTC+7 timezone — remove or adjust it based on your server’s timezone configuration. To filter jobs that ran longer than expected, add a WHERE duration_min > N clause.