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.

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:

  1. It only returns results for schedules that are enabled and attached to an enabled job
  2. 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:

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 NameSchedulesNext Run Date
Maintenance - Backups - SYSTEM - FULLEvery day at 7:30PM, starting on 05/12/2015; One time, occurs at 6:30PM on 08/13/2015, starting on 08/13/20158/13/2015 18:30
Maintenance - Backups - USER - DIFFEvery week on Monday, Tuesday, Wednesday, Thursday, Friday, Saturday at 4:00AM, starting on 05/12/20158/14/2015 4:00
Maintenance - Backups - USER - FULLEvery week on Sunday at 4:00AM, starting on 05/12/20158/16/2015 4:00
Maintenance - Backups - USER - LOGEvery day, every 1 hour(s) between 6:00AM and 2:59AM, starting on 05/12/20158/13/2015 17:00
Maintenance - CommandLog CleanupEvery week on Sunday at 11:00PM, starting on 05/12/20158/16/2015 23:00
Maintenance - DatabaseIntegrityCheck - SYSTEMEvery day at 7:00PM, starting on 05/12/20158/13/2015 19:00
Maintenance - DatabaseIntegrityCheck - USEREvery week on Sunday at 3:00AM, starting on 05/12/20158/16/2015 3:00
Maintenance - IndexOptimize - USEREvery week on Sunday at 2:00AM, starting on 05/12/20158/16/2015 2:00
Maintenance - Output File CleanupEvery week on Sunday at 11:00PM, starting on 05/12/20158/16/2015 23:00
Maintenance - sp_delete_backuphistoryEvery week on Sunday at 11:00PM, starting on 05/12/20158/16/2015 23:00
Maintenance - sp_purge_jobhistoryEvery week on Sunday at 11:00PM, starting on 05/12/20158/16/2015 23:00
syspolicy_purge_historyEvery day at 2:00AM, starting on 01/01/20088/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.

Leave a Reply

Your email address will not be published. Required fields are marked *