Rajanand Ilangovan
Rajanand Ilangovan

Rajanand Ilangovan

How to find a SQL Server agent jobs last executed and next scheduled date time?

Rajanand Ilangovan's photo
Rajanand Ilangovan
·Sep 8, 2022·

2 min read

You can execute the below query to get the list of SQL Server agents jobs and when it was last executed and time it took to complete the job and when it is scheduled to run next.

USE msdb;

-- List out all the SQL agent job in that server with details about when it was last executed,last run duration and when it is scheduled to run next.
;WITH cte_1 AS (
    SELECT 
    sj.name AS job_name,
    sj.description AS job_description,
    sj.enabled,
    sh.step_name,
    TRY_CAST(CAST(js.next_run_date AS NVARCHAR(20)) AS DATE) next_run_date,
    STUFF(STUFF(RIGHT(REPLICATE('0', 6) +  CAST(js.next_run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') next_run_time,
    TRY_CAST(CAST(sh.run_date AS NVARCHAR(20)) AS DATE) last_run_date,
    STUFF(STUFF(RIGHT(REPLICATE('0', 6) +  CAST(sh.run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') last_run_time,
    CASE WHEN sh.run_duration > 235959 THEN
            CAST((CAST(LEFT(CAST(sh.run_duration AS VARCHAR), LEN(CAST(sh.run_duration AS VARCHAR)) - 4) AS INT) / 24) AS VARCHAR)
            + '.' + RIGHT('00' + CAST(CAST(LEFT(CAST(sh.run_duration AS VARCHAR), LEN(CAST(sh.run_duration AS VARCHAR)) - 4) AS INT) % 24 AS VARCHAR), 2)
            + ':' + STUFF(CAST(RIGHT(CAST(sh.run_duration AS VARCHAR), 4) AS VARCHAR(6)), 3, 0, ':')
        ELSE STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(sh.run_duration AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
    END AS last_run_duration
    FROM msdb.dbo.sysjobs sj
    INNER JOIN msdb.dbo.sysjobhistory sh ON sj.job_id = sh.job_id
    LEFT JOIN msdb.dbo.sysjobschedules AS js ON sj.job_id = js.job_id
    WHERE sh.step_name = '(Job outcome)'
), cte_2 AS (
    SELECT 
    job_name,
    job_description,
    enabled,
    DATEADD(ms, DATEDIFF(ms, '00:00:00', last_run_time ), CONVERT(DATETIME, last_run_date)) AS last_run_datetime,
    last_run_duration,
    DATEADD(ms, DATEDIFF(ms, '00:00:00', next_run_time ), CONVERT(DATETIME, next_run_date)) AS next_run_datetime
    FROM cte_1
), cte_3 AS (
    SELECT 
    job_name,
    job_description,
    enabled,
    last_run_datetime,
    last_run_duration,
    next_run_datetime,
    rownum = DENSE_RANK() OVER(PARTITION BY job_name  ORDER BY last_run_datetime DESC)
    from cte_2
)
SELECT 
job_name,
job_description,
enabled,
last_run_datetime,
last_run_duration,
next_run_datetime
FROM cte_3
WHERE rownum = 1
ORDER BY last_run_datetime DESC, 
job_name ASC

GO

Output: image.png

Did you find this article valuable?

Support Rajanand Ilangovan by becoming a sponsor. Any amount is appreciated!

See recent sponsors Learn more about Hashnode Sponsors

Impressum