Scripts to get SQL agent jobs information
In this blog post, we’ll get the information of SQL Server Agent job schedules. This T-SQL commands and explore how they retrieve information about job schedules, recurrence patterns, and execution times.
This code has two parts one will gives a Daily schedulers of SQL server agent jobs and the other one gives a Weekly schedulers.
Part 1: Daily Schedules
The first part of the script focuses on daily schedules.
Part 2: Weekly Schedules
The second part of the script deals with weekly schedules.
SELECT sysjobs.NAME job_name,
sysjobs.enabled job_enabled,
sysschedules.NAME schedule_name,
sysschedules.freq_recurrence_factor,
CASE
WHEN freq_type = 4 THEN 'Daily'
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' + ' starting at '
+ Stuff(Stuff(RIGHT(Replicate('0', 6) +
Cast(
active_start_time AS VARCHAR(6)), 6), 3,
0, ':'
), 6, 0, ':')
WHEN freq_subday_type = 4 THEN ' every '
+ Cast(freq_subday_interval AS VARCHAR(7
))
+ ' minutes' + ' starting at '
+ Stuff(Stuff(RIGHT(Replicate('0', 6) +
Cast(
active_start_time AS VARCHAR(6)), 6), 3,
0, ':'
), 6, 0, ':')
WHEN freq_subday_type = 8 THEN ' every '
+ Cast(freq_subday_interval AS VARCHAR(7
))
+ ' hours' + ' starting at '
+ Stuff(Stuff(RIGHT(Replicate('0', 6) +
Cast(
active_start_time AS VARCHAR(6)), 6), 3,
0, ':'
), 6, 0, ':')
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.dbo.sysjobs
INNER JOIN msdb.dbo.sysjobschedules
ON sysjobs.job_id = sysjobschedules.job_id
INNER JOIN msdb.dbo.sysschedules
ON sysjobschedules.schedule_id = sysschedules.schedule_id
WHERE freq_type = 4
--Below is the script for weekly schedulers of SQL agent jobs..
SELECT sysjobs.NAME
job_name
,
sysjobs.enabled
job_enabled,
sysschedules.NAME
schedule_name,
sysschedules.freq_recurrence_factor,
CASE
WHEN freq_type = 8 THEN 'Weekly'
END
frequency,
Replace (CASE WHEN freq_interval&1 = 1 THEN 'Sunday, ' ELSE '' END + CASE
WHEN
freq_interval&2 = 2 THEN 'Monday, ' ELSE '' END + CASE WHEN
freq_interval&4 = 4 THEN 'Tuesday, ' ELSE '' END + CASE WHEN
freq_interval&8 = 8 THEN 'Wednesday, ' ELSE '' END + CASE WHEN
freq_interval&16 = 16 THEN 'Thursday, ' ELSE '' END + CASE WHEN
freq_interval&32 = 32 THEN 'Friday, ' ELSE '' END + CASE WHEN
freq_interval&64 = 64 THEN 'Saturday, ' ELSE '' END, ', ', '')
Days,
CASE
WHEN freq_subday_type = 2 THEN ' every '
+ Cast(freq_subday_interval AS VARCHAR(7
))
+ ' seconds' + ' starting at '
+ Stuff(Stuff(RIGHT(Replicate('0', 6) +
Cast(
active_start_time AS VARCHAR(6)), 6), 3,
0, ':'
), 6, 0, ':')
WHEN freq_subday_type = 4 THEN ' every '
+ Cast(freq_subday_interval AS VARCHAR(7
))
+ ' minutes' + ' starting at '
+ Stuff(Stuff(RIGHT(Replicate('0', 6) +
Cast(
active_start_time AS VARCHAR(6)), 6), 3,
0, ':'
), 6, 0, ':')
WHEN freq_subday_type = 8 THEN ' every '
+ Cast(freq_subday_interval AS VARCHAR(7
))
+ ' hours' + ' starting at '
+ Stuff(Stuff(RIGHT(Replicate('0', 6) +
Cast(
active_start_time AS VARCHAR(6)), 6), 3,
0, ':'
), 6, 0, ':')
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.dbo.sysjobs
INNER JOIN msdb.dbo.sysjobschedules
ON sysjobs.job_id = sysjobschedules.job_id
INNER JOIN msdb.dbo.sysschedules
ON sysjobschedules.schedule_id = sysschedules.schedule_id
WHERE freq_type = 8
ORDER BY job_enabled DESC
