What runs when? A readable SQL Agent schedule report decoded from msdb

During an incident or before a maintenance window, the question is always "what runs between 01:00 and 05:00?" SSMS shows one job at a time. This query shows them all, in words.

#How schedules are stored

msdb.dbo.sysschedules holds the schedule, msdb.dbo.sysjobschedules links it to jobs (and holds the next run time).

Sub-day repetition: freq_subday_type 1 = once at active_start_time, 2 = seconds, 4 = minutes, 8 = hours, every freq_subday_interval, between active_start_time and active_end_time. Times are HHMMSS integers.

#The report

USE msdb;
WITH s AS (
  SELECT sc.schedule_id, sc.name AS schedule_name, sc.enabled AS schedule_enabled, sc.freq_type, sc.freq_interval,
         sc.freq_subday_type, sc.freq_subday_interval, sc.freq_relative_interval, sc.freq_recurrence_factor,
         STUFF(STUFF(RIGHT('000000' + CAST(sc.active_start_time AS varchar(6)), 6), 5, 0, ':'), 3, 0, ':') AS start_t,
         STUFF(STUFF(RIGHT('000000' + CAST(sc.active_end_time   AS varchar(6)), 6), 5, 0, ':'), 3, 0, ':') AS end_t,
         sc.active_start_date
  FROM dbo.sysschedules sc),
days AS (   -- weekday names from the weekly bitmask
  SELECT s.schedule_id,
         STUFF(CONCAT(CASE WHEN s.freq_interval & 2  > 0 THEN ', Mon' END, CASE WHEN s.freq_interval & 4  > 0 THEN ', Tue' END,
                      CASE WHEN s.freq_interval & 8  > 0 THEN ', Wed' END, CASE WHEN s.freq_interval & 16 > 0 THEN ', Thu' END,
                      CASE WHEN s.freq_interval & 32 > 0 THEN ', Fri' END, CASE WHEN s.freq_interval & 64 > 0 THEN ', Sat' END,
                      CASE WHEN s.freq_interval & 1  > 0 THEN ', Sun' END), 1, 2, '') AS day_list
  FROM s WHERE s.freq_type = 8)
SELECT j.name AS job_name, j.enabled AS job_enabled, s.schedule_name, s.schedule_enabled,
  CONCAT(
    CASE s.freq_type
      WHEN 1   THEN CONCAT('Once on ', CONVERT(char(10), CONVERT(date, CONVERT(char(8), s.active_start_date)), 23))
      WHEN 4   THEN CASE WHEN s.freq_interval = 1 THEN 'Every day' ELSE CONCAT('Every ', s.freq_interval, ' days') END
      WHEN 8   THEN CONCAT(CASE WHEN s.freq_recurrence_factor > 1 THEN CONCAT('Every ', s.freq_recurrence_factor, ' weeks on ') ELSE 'Weekly on ' END, d.day_list)
      WHEN 16  THEN CONCAT('Day ', s.freq_interval, ' of every ', CASE WHEN s.freq_recurrence_factor > 1 THEN CONCAT(s.freq_recurrence_factor, ' months') ELSE 'month' END)
      WHEN 32  THEN CONCAT(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 day' END,
                           ' of every ', CASE WHEN s.freq_recurrence_factor > 1 THEN CONCAT(s.freq_recurrence_factor, ' months') ELSE 'month' END)
      WHEN 64  THEN 'When SQL Server Agent starts'
      WHEN 128 THEN 'When CPUs become idle'
    END,
    CASE WHEN s.freq_type IN (64, 128) THEN ''
         WHEN s.freq_subday_type = 1 THEN CONCAT(' at ', s.start_t)
         ELSE CONCAT(', every ', s.freq_subday_interval,
                     CASE s.freq_subday_type WHEN 2 THEN ' second(s)' WHEN 4 THEN ' minute(s)' WHEN 8 THEN ' hour(s)' END,
                     ' between ', s.start_t, ' and ', s.end_t) END) AS runs,
  CASE WHEN js.next_run_date > 0 THEN
    DATEADD(second, (js.next_run_time / 10000) * 3600 + (js.next_run_time / 100 % 100) * 60 + js.next_run_time % 100,
            CONVERT(datetime, CONVERT(char(8), js.next_run_date))) END AS next_run
FROM dbo.sysjobs j
JOIN dbo.sysjobschedules js ON js.job_id = j.job_id
JOIN s ON s.schedule_id = js.schedule_id
LEFT JOIN days d ON d.schedule_id = s.schedule_id
ORDER BY next_run, j.name;

Sample output:

next_run_date in sysjobschedules is refreshed by Agent roughly every 20 minutes, so it can lag right after a change. Use sp_help_jobschedule for the up-to-date value of one job.

#Useful variations

Jobs without any schedule (run only manually or from other jobs):

SELECT j.name FROM msdb.dbo.sysjobs j WHERE NOT EXISTS (SELECT 1 FROM msdb.dbo.sysjobschedules js WHERE js.job_id = j.job_id);

Shared schedules (one schedule used by several jobs, so changing it changes them all):

SELECT s.name, COUNT(*) AS jobs_using_it
FROM msdb.dbo.sysschedules s JOIN msdb.dbo.sysjobschedules js ON js.schedule_id = s.schedule_id
GROUP BY s.name HAVING COUNT(*) > 1;

What runs inside a window. Wrap the report in a CTE and filter next_run between the window start and end. For recurring sub-day jobs, also check start_t/end_t.

#Export for the run book

Invoke-DbaQuery -SqlInstance sql01 -Database msdb -File .\schedule-report.sql |
    Export-Csv "\\fileserver\runbooks\sql01-agent-schedules-$(Get-Date -Format yyyyMMdd).csv" -NoTypeInformation

Run it across all instances (-SqlInstance (Get-Content servers.txt)) to get one estate-wide schedule, which is the quickest way to spot clashing backup and maintenance windows.

Tip

Schedules are in the server's local time. On estates spread over time zones, add a column with the server's UTC offset (DATEDIFF(minute, GETUTCDATE(), GETDATE())), or the estate report will mislead you.