How to find a SQL Server agent jobs last executed and next scheduled date time?
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: