Menu
SQL Server Administration: Find the next time that any enabled job will run. Run the query below to return a list of jobs on any SQL Server along with the last run duration and next run time.
USE msdb
GO
SET NOCOUNT ON;
SELECT CONVERT(VARCHAR(20),SERVERPROPERTY(‘ServerName’)) AS ServerName,
j.name AS job_name,
CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration % 100 * 10) / 216e4,108) AS last_run_duration,
ja.next_scheduled_run_date
FROM msdb.dbo.sysjobactivity ja
LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id
join msdb.dbo.sysjobs_view j ON ja.job_id = j.job_id
WHERE ja.session_id=(SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity)
AND j.enabled = 1
ORDER BY job_name;
SET NOCOUNT OFF;
GO