Get a List and Schedule of SQL Server Jobs

Clearinghouse Repository Polaris Repository
, ,
1

This query pulls a list of SQL jobs set up in SQL Server along with all the information connected to them like frequency, start time, end time, intervals and so on. It’s particularly useful if you need to back up, clone, or reset a database system.

SELECT 
    j.name AS [Job Name],
    s.name AS [Schedule Name],
    s.enabled AS [ScheduleEnabled],
    s.freq_type AS [Frequency Type],
    s.freq_interval AS [Frequency Interval],
    s.freq_subday_type AS [Subday Type],
    s.freq_subday_interval AS [Subday Interval],
    s.freq_relative_interval AS [Relative Interval],
    s.freq_recurrence_factor AS [Recurrence Factor],
    s.active_start_date AS [Active Start Date],
    s.active_end_date AS [Active End Date],
    RIGHT('0' + CAST(s.active_start_time / 10000 AS VARCHAR(2)), 2) + ':' + 
    RIGHT('0' + CAST((s.active_start_time / 100) % 100 AS VARCHAR(2)), 2) + ':' + 
    RIGHT('0' + CAST(s.active_start_time % 100 AS VARCHAR(2)), 2) AS ActiveStartTime,
    RIGHT('0' + CAST(s.active_end_time / 10000 AS VARCHAR(2)), 2) + ':' + 
    RIGHT('0' + CAST((s.active_end_time / 100) % 100 AS VARCHAR(2)), 2) + ':' + 
    RIGHT('0' + CAST(s.active_end_time % 100 AS VARCHAR(2)), 2) AS ActiveEndTime,
    CASE 
        WHEN s.freq_type = 1 THEN 'One Time'
        WHEN s.freq_type = 4 THEN 'Daily'
        WHEN s.freq_type = 8 THEN 'Weekly'
        WHEN s.freq_type = 16 THEN 'Monthly'
        WHEN s.freq_type = 32 THEN 'Monthly, relative to frequency'
        WHEN s.freq_type = 64 THEN 'When SQL Server Agent starts'
        WHEN s.freq_type = 128 THEN 'When the computer is idle'
        ELSE 'Unknown'
    END AS FrequencyDescription
FROM 
    msdb.dbo.sysjobs j
INNER JOIN 
    msdb.dbo.sysjobschedules js
    ON (j.job_id = js.job_id)
INNER JOIN 
    msdb.dbo.sysschedules s
    ON (js.schedule_id = s.schedule_id)
ORDER BY 
    j.name,
    s.name;
2 Likes
2

For backup purposes, you can also dump all the jobs into a file using this method.

  1. Launch SSMS
  2. Navigate to the SQL Server Agent in the Object Explorer sidebar
  3. Hit the F7 key, this will open a new Object Explorer Window
  4. Shift click to highlight multiple custom jobs from the new Object Explorer window*
  5. Right click on the jobs then select Script Job → Create to → File and save them as a file.
    • This will save them all as a single file. You could open the file to extract specific jobs, but it is better to only backup your custom jobs, which is also why you should consistently prefix your custom job names, so if you need to restore them, you can just restore the entire batch.

*Note: You can only do the right click from this new Object Explorer window, not the Object Explorer sidebar.

image
image1278×612 24.8 KB

1 Like