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.
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.
