SQL 2005 - Get Job Schedules

• balasach82

  SSCrazy Eights

  Points: 9653

  More actions

  August 3, 2010 at 12:49 am

  #141363

  Need all job's schedules. The System tables/SP/View results are not clear (date/time etc). Is there a way to find out the scheduled run date/time without creating any UDF.

• balaji.ganga

  SSCrazy

  Points: 2280

  More actions

  August 3, 2010 at 1:26 am

  #1201377

  Hi,

  Check it sysjobs,sysjobschedules, sysschedules objects in msdb database.

  With thanks & regards

  Balaji.G

• Johan Bijnens

  SSC Guru

  Points: 135495

  More actions

  August 3, 2010 at 1:35 am

  #1201379

  maybe this can get you started ...

  select [Server]

  , JobNaam

  , JobEnabled

  --, last_run_date

  --, last_run_time

  --, Sum_last_run_duration

  , freq_type

  , frequence

  , Units_freq_subday_interval

  , freq_subday_interval

  , freq_relative_interval

  , freq_recurrence_factor

  , active_start_time

  , Schedule_Name

  , ScheduleEnabled

  from (

  select Srv.name as Server

  , J.name as JobNaam --, J.description

  , min(J.enabled) as JobEnabled

  , min(case SS.freq_type

  when 1 then 'Once'

  when 4 then 'Daily'

  when 8 then 'Weekly'

  when 16 then 'Monthly'

  when 32 then 'Monthly*'

  when 64 then 'At SqlServer Start'

  when 128 then 'CPU Idle'

  else '??????'

  end) as freq_type

  , min(case SS.freq_type

  when 1 then 'N/A'

  when 4 then convert(varchar(20), SS.freq_interval)

  when 8 then case when convert(binary(2), SS.freq_interval) & 1 = 1 then 'Su-'

  else '..-'

  end + case when convert(binary(2), SS.freq_interval) & 2 = 2 then 'Mo-'

  else '..-'

  end + case when convert(binary(2), SS.freq_interval) & 4 = 4 then 'Tu-'

  else '..-'

  end + case when convert(binary(2), SS.freq_interval) & 8 = 8 then 'We-'

  else '..-'

  end + case when convert(binary(2), SS.freq_interval) & 16 = 16 then 'Th-'

  else '..-'

  end + case when convert(binary(2), SS.freq_interval) & 32 = 32 then 'Fr-'

  else '..-'

  end + case when convert(binary(2), SS.freq_interval) & 64 = 64 then 'Sa'

  else '..'

  end

  when 16 then convert(varchar(20), SS.freq_interval)

  when 32 then case when convert(binary(2), SS.freq_interval) & 1 = 1 then 'Su-'

  else '..-'

  end + case when convert(binary(2), SS.freq_interval) & 2 = 2 then 'Mo-'

  else '..-'

  end + case when convert(binary(2), SS.freq_interval) & 3 = 3 then 'Tu-'

  else '..-'

  end + case when convert(binary(2), SS.freq_interval) & 4 = 4 then 'We-'

  else '..-'

  end + case when convert(binary(2), SS.freq_interval) & 5 = 5 then 'Th-'

  else '..-'

  end + case when convert(binary(2), SS.freq_interval) & 6 = 6 then 'Fr-'

  else '..-'

  end + case when convert(binary(2), SS.freq_interval) & 7 = 7 then 'Sa'

  else '..'

  end + case when convert(binary(2), SS.freq_interval) & 8 = 8 then 'Day-'

  else '...'

  end + case when convert(binary(2), SS.freq_interval) & 9 = 9 then 'Weekday-'

  else '..'

  end + case when convert(binary(2), SS.freq_interval) & 10 = 10 then 'Weekend'

  else '..'

  end

  when 64 then convert(varchar(20), SS.freq_interval) + ' ???'

  else '??????'

  end) as frequence

  , min(case SS.freq_subday_type

  when 1 then 'At the specified time'

  when 2 then 'Seconds'

  when 4 then 'Minutes'

  when 8 then 'Hours'

  else '???'

  end) as Units_freq_subday_interval

  , min(SS.freq_subday_interval) as freq_subday_interval

  , min(case SS.freq_type

  when 32 then case freq_relative_interval --int Scheduled job's occurrence of the freq_interval in each month when freq_type is 32 (monthly relative):

  when 1 then 'First'

  when 2 then 'Second'

  when 4 then 'Third'

  when 8 then 'Fourth'

  when 16 then 'Last'

  else '???'

  end

  else '-NA-'

  end) as freq_relative_interval

  , min(SS.freq_recurrence_factor) as freq_recurrence_factor

  , min(SS.active_start_time) as active_start_time

  , SS.name as Schedule_Name

  , min(SS.enabled) as ScheduleEnabled

  -- select *

  from msdb.dbo.sysjobs J

  inner join msdb.dbo.sysjobschedules S

  on J.job_id = S.job_id

  inner join msdb.dbo.sysschedules SS

  on SS.schedule_id = S.schedule_id

  inner join sys.servers Srv

  on Srv.server_id = J.originating_server_id

  group by Srv.name

  , J.name

  , SS.name

  ) GroupSel

  order by Jobnaam

  /* only the schedules */

  Select *

  from msdb.dbo.sysschedules SS

  order by name

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me

• Carl Grant

  SSChasing Mays

  Points: 612

  More actions

  June 28, 2012 at 5:18 pm

  #1506893

  Thanks for this script!