SQL agent job schedules

Question

SQL agent job schedules

SQLAssAS

SSCertifiable

Points: 7246
More actions
April 19, 2018 at 1:22 am

#403279

Does anyone know where the Description field in the SQL Job schedule is stored within MSDB?

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

SQLAssAS SSCertifiable Points: 7246 More actions · Answer 1

Sorted 🙂

select
sysjobs.name [Job Name]
,CASE sysjobs.enabled
        when 1 THEN 'Job Enabled'
        when 0 then 'Job Not Enabled'
    End job_enabled
,CASE sysschedules.enabled
        WHEN 1 Then 'Schedule Enabled'
        WHEN 0 THEN 'Schedule Not Enabled'
    end schedule_enabled
,CASE sysschedules.[freq_type]
   WHEN 1 THEN 'One Time'
   WHEN 4 THEN 'Daily'
   WHEN 8 THEN 'Weekly'
   WHEN 16 THEN 'Monthly'
   WHEN 32 THEN 'Monthly - Relative to Frequency Interval'
   WHEN 64 THEN 'Start automatically when SQL Server Agent starts'
   WHEN 128 THEN 'Start whenever the CPUs become idle'
  END [Occurrence]
,CASE sysschedules.[freq_subday_type]
   WHEN 1 THEN 'Occurs once at '
       + STUFF(
      STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
           , 3, 0, ':')
          , 6, 0, ':')
   WHEN 2 THEN 'Occurs every '
       + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Second(s) between '
       + STUFF(
       STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
           , 3, 0, ':')
          , 6, 0, ':')
       + ' & '
       + STUFF(
       STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
           , 3, 0, ':')
          , 6, 0, ':')
   WHEN 4 THEN 'Occurs every '
       + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Minute(s) between '
       + STUFF(
       STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
           , 3, 0, ':')
          , 6, 0, ':')
       + ' & '
       + STUFF(
       STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
           , 3, 0, ':')
          , 6, 0, ':')
   WHEN 8 THEN 'Occurs every '
       + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Hour(s) between '
       + STUFF(
       STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
           , 3, 0, ':')
          , 6, 0, ':')
       + ' & '
       + STUFF(
       STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
           , 3, 0, ':')
          , 6, 0, ':')
  END [Frequency]
from msdb.dbo.sysjobs
inner join msdb.dbo.sysjobschedules on sysjobs.job_id = sysjobschedules.job_id
inner join msdb.dbo.sysschedules on sysjobschedules.schedule_id = sysschedules.schedule_id