Getting SQL Job Schedules
In my ever expanding mission to document and inventory my SQL environment, I wanted a way to get human-readable schedule information about my jobs. While initially this seemed like it would be a fairly trivial exercise, it turned out to be a little more involved. Its possible there is an easier way to do this, and if you know of one let me know. I will go through the interesting sections as I’ve done in the past, with a link to the full script at the end.
Here is the main meat of the script, which takes the machine-readable scheduling information and converts it back into something a bit easier for a human to understand.
IF OBJECT_ID('tempdb..#tmpjobschedules') IS NOT NULL
DROP TABLE #tmpjobschedules
SELECT @@SERVERNAME as [Server], j.Name as [Job Name],
-- Type of Schedule
CASE freq_type
WHEN 1 THEN 'One time, occurs at ' + CONVERT(varchar(15), CONVERT(time, STUFF(STUFF(RIGHT('000000' + CONVERT(varchar(6), active_start_time), 6), 3, 0, ':'), 6, 0, ':')), 100) + ' on ' + CONVERT(varchar, CONVERT(datetime,CONVERT(char(8), s.active_start_date)), 101)
WHEN 64 THEN 'When SQL Server Agent Service starts'
WHEN 128 THEN 'When the Server is idle'
ELSE ''
END +
-- Frequency of type
CASE
WHEN freq_type = 4 THEN 'Every ' +
CASE s.freq_interval
WHEN 1 THEN 'day'
ELSE CONVERT(varchar, s.freq_interval) + ' day(s)'
END
WHEN freq_type = 8 THEN 'Every ' +
CASE s.freq_recurrence_factor
WHEN 1 THEN 'week on '
ELSE CONVERT(varchar, s.freq_recurrence_factor) + ' week(s) on '
END +
REPLACE(RTRIM(
CASE WHEN s.freq_interval & 1 = 1 THEN 'Sunday ' ELSE '' END +
CASE WHEN s.freq_interval & 2 = 2 THEN 'Monday ' ELSE '' END +
CASE WHEN s.freq_interval & 4 = 4 THEN 'Tuesday ' ELSE '' END +
CASE WHEN s.freq_interval & 8 = 8 THEN 'Wednesday ' ELSE '' END +
CASE WHEN s.freq_interval & 16 = 16 THEN 'Thursday ' ELSE '' END +
CASE WHEN s.freq_interval & 32 = 32 THEN 'Friday ' ELSE '' END +
CASE WHEN s.freq_interval & 64 = 64 THEN 'Saturday ' ELSE '' END), ' ', ', ')
WHEN freq_type = 16 THEN 'Every ' +
CASE s.freq_recurrence_factor
WHEN 1 THEN 'month on day '
ELSE CONVERT(varchar, s.freq_recurrence_factor) + ' month(s) on day '
END + CONVERT(varchar(2), s.freq_interval)
WHEN freq_type = 32 THEN 'Every ' +
CASE s.freq_recurrence_factor
WHEN 1 THEN 'month on the '
ELSE CONVERT(varchar, s.freq_recurrence_factor) + ' month(s) on the '
END +
CASE s.freq_relative_interval WHEN 1 THEN 'first ' WHEN 2 THEN 'second ' WHEN 4 THEN 'third ' WHEN 8 THEN 'fourth ' WHEN 16 THEN 'last ' END +
CASE s.freq_interval WHEN 1 THEN 'Sunday' WHEN 2 THEN 'Monday' WHEN 3 THEN 'Tuesday' WHEN 4 THEN 'Wednesday' WHEN 5 THEN 'Thursday' WHEN 6 THEN 'Friday' WHEN 7 THEN 'Saturday' WHEN 8 THEN 'day' WHEN 9 THEN 'weekday' WHEN 10 THEN 'weekend' END
ELSE ''
END +
-- Frequency of time
CASE s.freq_subday_type
WHEN 1 THEN ' at ' + CONVERT(varchar(15), CONVERT(time, STUFF(STUFF(RIGHT('000000' + CONVERT(varchar(6), active_start_time), 6), 3, 0, ':'), 6, 0, ':')), 100)
WHEN 2 THEN ', every ' + CONVERT(varchar, freq_subday_interval) + ' second(s)'
WHEN 4 THEN ', every ' + CONVERT(varchar, freq_subday_interval) + ' minute(s)'
WHEN 8 THEN ', every ' + CONVERT(varchar, freq_subday_interval) + ' hour(s)'
ELSE ''
END +
-- Time bounds
CASE s.freq_subday_type
WHEN 0 THEN ''
WHEN 1 THEN ''
ELSE ' between ' + CONVERT(varchar(15), CONVERT(time, STUFF(STUFF(RIGHT('000000' + CONVERT(varchar(6),s.active_start_time),6 ),3,0,':'),6,0,':')), 100) + ' and ' + CONVERT(varchar(15), CONVERT(time, STUFF(STUFF(RIGHT('000000' + CONVERT(varchar(6),active_end_time),6 ),3,0,':'),6,0,':')), 100)
END +
-- Date bounds
', starting on ' + CONVERT(varchar, CONVERT(datetime,CONVERT(char(8), s.active_start_date)), 101) +
CASE active_end_date
WHEN '99991231' THEN ''
ELSE ' and ending on ' + CONVERT(varchar, CONVERT(datetime,CONVERT(char(8), s.active_end_date)), 101)
END AS [Schedule],
CASE js.next_run_date WHEN 0 THEN NULL ELSE CONVERT(varchar, msdb.dbo.agent_datetime(js.next_run_date, js.next_run_time), 120) END AS [Next Run Date]
INTO #tmpjobschedules
FROM msdb.dbo.sysjobs j
LEFT OUTER JOIN msdb.dbo.sysjobschedules js on j.job_id = js.job_id
LEFT OUTER JOIN msdb.dbo.sysschedules s on js.schedule_id = s.schedule_id
WHERE j.enabled = 1 and s.enabled = 1
ORDER BY j.name ASC
This chunk of code stores the instance name, job name, schedule description, and next run date. Couple things to note, which is why this isn’t the entire script:
- It only returns results for schedules that are enabled and attached to an enabled job
- Jobs with multiple schedules attached to them return multiple rows
Since SQL Server stores date and time information in ugly INT form, I had to do some padding with zeros and STUFFing of colons in order to convert the INTs into DATEs, TIMEs, and DATETIMEs. Those functions make up a large bit of the code. Most of the other INTs the scheduling information is stored in is a simple replacement, which I handled with CASE statements. The most interesting of these operations if for weekly schedules. Since it is possible to pick multiple days of the week in these type of schedules, I needed to utilize the bitwise AND (&) as well as some concatenation to get the proper results.
In order to get the results in the form I wanted (one entry for each job, regardless of how many schedules it has), I have to do a little more magic:
SELECT j.[name] AS [Job Name], CASE
WHEN STUFF((
SELECT '; ' + s.Schedule
FROM #tmpjobschedules s
WHERE j.name = s.[Job Name]
FOR XML PATH ('')), 1, 2, '')
IS NULL THEN 'Not Scheduled'
ELSE STUFF((
SELECT '; ' + s.Schedule
FROM #tmpjobschedules s
WHERE j.name = s.[Job Name]
FOR XML PATH ('')), 1, 2, '') END AS [Schedules],
(SELECT MIN(s.[Next Run Date]) FROM #tmpjobschedules s WHERE j.[name] = s.[Job Name]) AS [Next Run Date]
FROM msdb.dbo.sysjobs j
Basically what this is doing is going through each job in msdb.dbo.sysjobs, then returning all the schedules separated by semicolons. It does this by using some FOR XML PATH magic, which when combined with the SELECT ‘; ‘ + s.Schedule produces a semicolon-seperated result in a single row. The empty string row element makes eliminates the normal XML wrapping tags. THe STUFF is then used to replace the very first 2 characters with an empty sting (since they will be ‘; ‘). After that, there is just a little MIN calculation done which pulls the closes next run date/time from all of the schedules for each job. One thing to note about this is the base table it pulls from (msdb.dbo.sysjobschedules) only refreshes every 20 minutes, so this value may not always be accurate.
Here are some sample results:
| Job Name | Schedules | Next Run Date |
|---|---|---|
| Maintenance - Backups - SYSTEM - FULL | Every day at 7:30PM, starting on 05/12/2015; One time, occurs at 6:30PM on 08/13/2015, starting on 08/13/2015 | 8/13/2015 18:30 |
| Maintenance - Backups - USER - DIFF | Every week on Monday, Tuesday, Wednesday, Thursday, Friday, Saturday at 4:00AM, starting on 05/12/2015 | 8/14/2015 4:00 |
| Maintenance - Backups - USER - FULL | Every week on Sunday at 4:00AM, starting on 05/12/2015 | 8/16/2015 4:00 |
| Maintenance - Backups - USER - LOG | Every day, every 1 hour(s) between 6:00AM and 2:59AM, starting on 05/12/2015 | 8/13/2015 17:00 |
| Maintenance - CommandLog Cleanup | Every week on Sunday at 11:00PM, starting on 05/12/2015 | 8/16/2015 23:00 |
| Maintenance - DatabaseIntegrityCheck - SYSTEM | Every day at 7:00PM, starting on 05/12/2015 | 8/13/2015 19:00 |
| Maintenance - DatabaseIntegrityCheck - USER | Every week on Sunday at 3:00AM, starting on 05/12/2015 | 8/16/2015 3:00 |
| Maintenance - IndexOptimize - USER | Every week on Sunday at 2:00AM, starting on 05/12/2015 | 8/16/2015 2:00 |
| Maintenance - Output File Cleanup | Every week on Sunday at 11:00PM, starting on 05/12/2015 | 8/16/2015 23:00 |
| Maintenance - sp_delete_backuphistory | Every week on Sunday at 11:00PM, starting on 05/12/2015 | 8/16/2015 23:00 |
| Maintenance - sp_purge_jobhistory | Every week on Sunday at 11:00PM, starting on 05/12/2015 | 8/16/2015 23:00 |
| syspolicy_purge_history | Every day at 2:00AM, starting on 01/01/2008 | 8/14/2015 2:00 |
The full script can be found here. I also have a similar script which generates the schedule descriptions as they show up in SSMS here. That version will return multiple lines for jobs with multiple scheduled, however. It also returns whether or not the job/schedule is disabled.