SQL Agent Job Schedules

Many moons ago, I posted a script to help report on the human friendly scheduling information for SSRS subscriptions. You can read all about that here.

In a very similar fashion, today I want to share how easy it is to turn the internal schedules for SQL Agent jobs into something that is more human friendly. SQL Agent job schedules should be easy to read for humans if for nothing else than the fact that you have to provide this information to the auditors from time to time. We all know they can’t properly interpret the numeric job schedules and properly determine what they mean.

The job schedules work quite well with the current design – which is not human friendly. The use of numbers is a streamlined approach and far more efficient. It may be a bit more useful to us if there were some sort of translation table that was stored. A stored translation table would make it terribly easy to figure out what is happening within the schedules for the SQL Agent jobs. We don’t have that, so we often have to do a little trial and error. Or we rely on people that have done that trial and error – with a little verification on our part. Afterall, these scripts are posted on the internet.

I am not going to break down the script. With the similarity to the aforementioned SSRS script, I would recommend looking there for a little bit of an explanation. Here is the script to retrieve SQL Agent Job Schedules from your MSDB instance.

USE msdb;
GO
DECLARE @weekDay TABLE
(
mask INT
, maskValue VARCHAR(32)
);
INSERT INTO @weekDay
SELECT 1
, 'Sunday'
UNION ALL
SELECT 2
, 'Monday'
UNION ALL
SELECT 4
, 'Tuesday'
UNION ALL
SELECT 8
, 'Wednesday'
UNION ALL
SELECT 16
, 'Thursday'
UNION ALL
SELECT 32
, 'Friday'
UNION ALL
SELECT 64
, 'Saturday';
WITHmyCTE
AS ( SELECT sched.name AS 'scheduleName'
, sched.enabled AS SchedEnabled
, sched.schedule_id
, jobsched.job_id
, CASEWHEN sched.freq_type = 1 THEN 'Once'
WHEN sched.freq_type = 4
AND sched.freq_interval = 1
THEN 'Daily'
WHEN sched.freq_type = 4
THEN 'Every '
+ CAST(sched.freq_interval AS VARCHAR(5))
+ ' days'
WHEN sched.freq_type = 8
THEN REPLACE(REPLACE(REPLACE(( SELECT maskValue
FROM @weekDay AS x
WHERE sched.freq_interval
& x.mask <> 0
ORDER BY mask
FOR
XML RAW
),
'"/><row maskValue="',
', '),
'<row maskValue="',
''), '"/>', '')
+ CASEWHEN sched.freq_recurrence_factor <> 0
AND sched.freq_recurrence_factor = 1
THEN '; weekly'
WHEN sched.freq_recurrence_factor <> 0
THEN '; every '
+ CAST(sched.freq_recurrence_factor AS VARCHAR(10))
+ ' weeks'
END
WHEN sched.freq_type = 16
THEN 'On day '
+ CAST(sched.freq_interval AS VARCHAR(10))
+ ' of every '
+ CAST(sched.freq_recurrence_factor AS VARCHAR(10))
+ ' months'
WHEN sched.freq_type = 32
THEN CASEWHEN sched.freq_relative_interval = 1
THEN 'First'
WHEN sched.freq_relative_interval = 2
THEN 'Second'
WHEN sched.freq_relative_interval = 4
THEN 'Third'
WHEN sched.freq_relative_interval = 8
THEN 'Fourth'
WHEN sched.freq_relative_interval = 16
THEN 'Last'
END
+ CASEWHEN sched.freq_interval = 1
THEN ' Sunday'
WHEN sched.freq_interval = 2
THEN ' Monday'
WHEN sched.freq_interval = 3
THEN ' Tuesday'
WHEN sched.freq_interval = 4
THEN ' Wednesday'
WHEN sched.freq_interval = 5
THEN ' Thursday'
WHEN sched.freq_interval = 6
THEN ' Friday'
WHEN sched.freq_interval = 7
THEN ' Saturday'
WHEN sched.freq_interval = 8
THEN ' Day'
WHEN sched.freq_interval = 9
THEN ' Weekday'
WHEN sched.freq_interval = 10
THEN ' Weekend'
END
+ CASEWHEN sched.freq_recurrence_factor <> 0
AND sched.freq_recurrence_factor = 1
THEN '; monthly'
WHEN sched.freq_recurrence_factor <> 0
THEN '; every '
+ CAST(sched.freq_recurrence_factor AS VARCHAR(10))
+ ' months'
END
WHEN sched.freq_type = 64 THEN 'StartUp'
WHEN sched.freq_type = 128 THEN 'Idle'
END AS Frequency
, ISNULL('Every '
+ CAST(sched.freq_subday_interval AS VARCHAR(10))
+ CASEWHEN sched.freq_subday_type = 2
THEN ' seconds'
WHEN sched.freq_subday_type = 4
THEN ' minutes'
WHEN sched.freq_subday_type = 8
THEN ' hours'
END, 'Once') AS SubFrequency
, REPLICATE('0', 6 - LEN(sched.active_start_time))
+ CAST(sched.active_start_time AS VARCHAR(6)) AS start_time
, REPLICATE('0', 6 - LEN(sched.active_end_time))
+ CAST(sched.active_end_time AS VARCHAR(6)) AS end_time
, REPLICATE('0', 6 - LEN(jobsched.next_run_time))
+ CAST(jobsched.next_run_time AS VARCHAR(6)) AS next_run_time
, CAST(jobsched.next_run_date AS CHAR(8)) AS next_run_date
FROM msdb.dbo.sysschedules AS sched
INNER JOIN msdb.dbo.sysjobschedules AS jobsched
ON sched.schedule_id = jobsched.schedule_id
WHERE sched.enabled = 1
)
SELECT j.name AS JobName
, j.enabled
, j.category_id
, sp.name AS JobOwner
, c.name
, c.category_class
, js.step_id
, js.step_name
, js.subsystem
, js.command
, js.database_name
, js.database_user_name
, ct.next_run_date
, ct.next_run_time
, ct.start_time
, ct.end_time
, ct.Frequency
, ct.SubFrequency
, ct.scheduleName AS ScheduleName
, ct.SchedEnabled--, ss.
FROM dbo.sysjobs j
INNER JOIN dbo.sysjobsteps js
ON j.job_id = js.job_id
INNER JOIN dbo.syscategories c
ON j.category_id = c.category_id
INNER JOIN sys.server_principals sp
ON j.owner_sid = sp.sid
INNER JOIN myCTE ct
ON ct.job_id = j.job_id;

This script will only retrieve the SQL Agent Job schedules that are not SSRS related and that are not SSIS maintenance package related. The SSRS stuff has already been introduced, and a quick eval of those blog posts will show the differences between grabbing SSRS information vs. grabbing just SQL Agent Jobs.

Stay tuned. I will post a script for the SSIS related scheduling information.

Now, go forth and audit your agent jobs. Document the schedules in an easy to read format. Give that report / document to the auditors and your manager and look like a rock star for the day!

Table Hierarchy updated January 21, 2014
Database In Recovery Update May 18, 2015
Day 10 – Retention of XE Session Data in a Table January 3, 2014
Supported Compatibility Levels in SQL Server May 21, 2014
Backup Deployed Sessions October 5, 2015

SQL Agent Job Schedules