Master of None

Sometimes there is a need to see last run time information for a SQL Agent job. You may need to see when the job is scheduled to run next.  The msdb system database contains a ton of information about SQL Agent jobs schedules, activity, job steps and etc..

I created the below query to get a bird’s eye view of job executions. The query is based off msdb.dbo.sysjobs and msdb.dbo.sysjobactivity. These objects have a lot more information that can be added. Tweak the results to meet your needs.

The query below determines if a job is enabled, when it is scheduled to run( or not), last start time (or not), last time it completed and duration in minutes.The results have been limited to job scheduled to run today or in the future and a specific job named ‘Your Job Name Goes Here’. There is not a dependency for either of these items in the where clause.

SET NOCOUNT ON 
USE msdb
GO
-- Find Jobs next run date with last and time
-- jackdonnell.com

SELECT
 j.name
 ,case when j.enabled = 1 then 'yes' else 'no' end [enabled]
 ,ja.[next_scheduled_run_date]
 ,ja.[start_execution_date]
 ,ja.[stop_execution_date]
 ,datepart(minute,(ja.[stop_execution_date] - ja.[start_execution_date])) as [last_run_minutes]
FROM msdb.dbo.sysjobs j 
INNER JOIN [msdb].[dbo].[sysjobactivity] ja 
 ON ja.[job_id] = j.job_id 
WHERE ja.[next_scheduled_run_date] > CONVERT(VARCHAR(10), getdate(), 121)
AND j.[name] = 'Your Job Name Goes Here' -- Filter to specific job name
ORDER BY j.[name],ja.[next_scheduled_run_date]
GO

Tiny URL for this post:
 Copy