Find SQL scheduled jobs to run during a specific time range

There are times when we need to know which jobs are going to run in the upcoming half an hour or so. It is quite possible and easy when you invoke the job activity monitor. However, if you want to know through a query or if you want to make a report, here is the code.

SELECT j.name,
dbo.agent_datetime(next_run_date, next_run_time),
STUFF(STUFF(RIGHT(replicate(‘0’, 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ‘:’), 6, 0, ‘:’),
STUFF(STUFF(RIGHT(replicate(‘0’, 6) + CAST(active_end_time AS VARCHAR(6)), 6), 3, 0, ‘:’), 6, 0, ‘:’),
              CASE
               WHEN freq_type = 4 THEN ‘Daily’
               WHEN freq_type = 8 THEN ‘Weekly’
               WHEN freq_type = 16 THEN ‘Monthly’
               WHEN freq_type = 32 THEN ‘Monthly’
              END frequency,
              ‘every ‘ + CAST (freq_interval AS VARCHAR(3)) + ‘ day(s)’  Days,
              CASE
               WHEN freq_subday_type = 2
               THEN ‘ every ‘ + CAST(freq_subday_interval AS VARCHAR(7)) + ‘ seconds’
               WHEN freq_subday_type = 4
               THEN ‘ every ‘ + CAST(freq_subday_interval AS VARCHAR(7)) + ‘ minutes’
               WHEN freq_subday_type = 8
               THEN ‘ every ‘ + CAST(freq_subday_interval AS VARCHAR(7)) + ‘ hours’
               else ‘ starting at ‘ +
               STUFF(STUFF(RIGHT(replicate(‘0’, 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ‘:’), 6, 0, ‘:’)
               END
               time
FROM msdb..sysjobschedules S
JOIN msdb..sysjobs J ON S.job_id = J.job_id
JOIN msdb..sysschedules sc ON sc.schedule_id = s.schedule_id
WHERE
CONVERT(VARCHAR(10), dbo.agent_datetime(next_run_date, next_run_time),101)=
CONVERT(VARCHAR(10), GETDATE(),101)
AND next_run_date > 0
AND next_run_time > 0 AND
CONVERT(VARCHAR(5),dbo.agent_datetime(next_run_date,next_run_time),114)>=’16:00′
AND
CONVERT(VARCHAR(5),dbo.agent_datetime(next_run_date,next_run_time),114)<=’20:30′
AND j.enabled = 1
ORDER BY dbo.agent_datetime(next_run_date, next_run_time) 

This will return the data in the below format.
Job Run
Name: Name of the job

Next run date-time: Next occurrence of the job that falls between the time frame mentioned in the query i.e., 16:00 to 20:30 hours.

Active Start Time: Job starting point in the day.

Active End Time: Job ending point in the day.

Frequency: Daily/Weekly/Monthly

Days: Scheduled days (every day, every 2 days, every x number of days, etc.)

Time: Interval between each occurrence.


One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s