SQL Agent job schedules

Last week I found myself reviewing a SQL Server (2000 vintage no less!). As part of the review I was asked to document the Agent jobs - when they ran, how long for etc, etc. Unfortunately there were well over 100 of them. Clearly a query was called for - and to save me from having to repeat the exercise I'll reproduce it here:

This is the 2000 version:

select j.name,
case freq_type when 1 then 'Once'
when 4 then 'Daily'
when 8 then 'Weekly'
when 16 then 'Monthly'
when 32 then 'Monthly Relative'
else 'On start' end as TypeOfFrequency,
case freq_type when 1 then 'Once'
when 4 then 'Daily'
when 8 then 'Weekly'
when 16 then 'Monthly'
when 32 then 'Monthly Relative'
else 'On start' end,
case when freq_type=4 then
case when freq_interval=1 then 'Every day'
else 'Every '+cast(freq_interval as varchar(2))+' days' end
when freq_type=8 then 'Every '+
left(
case when 1|freq_interval=freq_interval then 'Sunday, ' else '' end
+case when 2|freq_interval=freq_interval then 'Monday, ' else '' end
+case when 4|freq_interval=freq_interval then 'Tuesday, ' else '' end
+case when 8|freq_interval=freq_interval then 'Wednesday, ' else '' end
+case when 16|freq_interval=freq_interval then 'Thursday, ' else '' end
+case when 32|freq_interval=freq_interval then 'Friday, ' else '' end
+case when 64|freq_interval=freq_interval then 'Saturday,' else '' end,
len(case when 1|freq_interval=freq_interval then 'Sunday, ' else '' end
+case when 2|freq_interval=freq_interval then 'Monday, ' else '' end
+case when 4|freq_interval=freq_interval then 'Tuesday, ' else '' end
+case when 8|freq_interval=freq_interval then 'Wednesday, ' else '' end
+case when 16|freq_interval=freq_interval then 'Thursday, ' else '' end
+case when 32|freq_interval=freq_interval then 'Friday, ' else '' end
+case when 64|freq_interval=freq_interval then 'Saturday,' else '' end)-1)
when freq_type=16 then 'On day '+cast(freq_interval as varchar(2))+' of the month'
else 'The '+
case freq_relative_interval when 1 then 'first '
when 2 then 'second '
when 4 then 'third '
when 8 then 'forth '
else 'last ' end
+'day of the month'
end,
case freq_subday_type when 1
then 'At ' + reverse(left(reverse(next_run_time), 2)+':'
+ substring(reverse(next_run_time), 3, 2)+':'
+ substring(reverse(next_run_time), 5, 2))

when 2 then 'Every '+cast(freq_subday_interval as varchar(1000))+' seconds'
when 4 then 'Every '+cast(freq_subday_interval as varchar(1000))+' mins'
else 'Every ' +cast(freq_subday_interval as varchar(1000))+' hours'
end as 'Daily execution (hhmmss)',
avg(run_duration) AvgDuration,
min(run_duration) MinDuration,
max(run_duration) MaxDuration,
count(*)
from sysjobs j
inner join sysjobhistory h on j.job_id=h.job_id
inner join sysjobschedules s on j.job_id=s.job_id
group by
j.name,
case freq_type when 1 then 'Once'
when 4 then 'Daily'
when 8 then 'Weekly'
when 16 then 'Monthly'
when 32 then 'Monthly Relative'
else 'On start' end,
case when freq_type=4 then
case when freq_interval=1 then 'Every day' else 'Every '+cast(freq_interval as varchar(2))+' days' end
when freq_type=8 then 'Every '+
left(case when 1|freq_interval=freq_interval then 'Sunday, ' else '' end
+case when 2|freq_interval=freq_interval then 'Monday, ' else '' end
+case when 4|freq_interval=freq_interval then 'Tuesday, ' else '' end
+case when 8|freq_interval=freq_interval then 'Wednesday, ' else '' end
+case when 16|freq_interval=freq_interval then 'Thursday, ' else '' end
+case when 32|freq_interval=freq_interval then 'Friday, ' else '' end
+case when 64|freq_interval=freq_interval then 'Saturday,' else '' end,
len(case when 1|freq_interval=freq_interval then 'Sunday, ' else '' end
+case when 2|freq_interval=freq_interval then 'Monday, ' else '' end
+case when 4|freq_interval=freq_interval then 'Tuesday, ' else '' end
+case when 8|freq_interval=freq_interval then 'Wednesday, ' else '' end
+case when 16|freq_interval=freq_interval then 'Thursday, ' else '' end
+case when 32|freq_interval=freq_interval then 'Friday, ' else '' end
+case when 64|freq_interval=freq_interval then 'Saturday,' else '' end)-1)
when freq_type=16 then 'On day '+cast(freq_interval as varchar(2))+' of the month'
else 'The '+
case freq_relative_interval when 1 then 'first '
when 2 then 'second '
when 4 then 'third '
when 8 then 'forth '
else 'last ' end
+'day of the month'
end,
case freq_subday_type when 1 then 'At ' + reverse(left(reverse(next_run_time), 2)+':'
+ substring(reverse(next_run_time), 3, 2)+':'
+ substring(reverse(next_run_time), 5, 2))
when 2 then 'Every '+cast(freq_subday_interval as varchar(1000))+' seconds'
when 4 then 'Every '+cast(freq_subday_interval as varchar(1000))+' mins'
else 'Every ' +cast(freq_subday_interval as varchar(1000))+' hours'
end

order by j.name

Nasty huh! Not sure why bitmask values are being used where they aren't combined (ie freq_relative_interval) but anyway... Note also the smart-alec use of the bitwise OR operator to get the days of the week.

This is the 2005/8 version. Note that since you can now have multiple schedules per job, you might get multiple rows per job - hence the use of 'ScheduleName':

select j.name,
s.name as ScheduleName,
case freq_type when 1 then 'Once'
when 4 then 'Daily'
when 8 then 'Weekly'
when 16 then 'Monthly'
when 32 then 'Monthly Relative'
else 'On start' end as TypeOfFrequency,
case when freq_type=4 then
case when freq_interval=1 then 'Every day' else 'Every '+cast(freq_interval as varchar(2))+' days' end
when freq_type=8 then 'Every '+
left(
case when 1|freq_interval=freq_interval then 'Sunday, ' else '' end
+case when 2|freq_interval=freq_interval then 'Monday, ' else '' end
+case when 4|freq_interval=freq_interval then 'Tuesday, ' else '' end
+case when 8|freq_interval=freq_interval then 'Wednesday, ' else '' end
+case when 16|freq_interval=freq_interval then 'Thursday, ' else '' end
+case when 32|freq_interval=freq_interval then 'Friday, ' else '' end
+case when 64|freq_interval=freq_interval then 'Saturday,' else '' end,
len(case when 1|freq_interval=freq_interval then 'Sunday, ' else '' end
+case when 2|freq_interval=freq_interval then 'Monday, ' else '' end
+case when 4|freq_interval=freq_interval then 'Tuesday, ' else '' end
+case when 8|freq_interval=freq_interval then 'Wednesday, ' else '' end
+case when 16|freq_interval=freq_interval then 'Thursday, ' else '' end
+case when 32|freq_interval=freq_interval then 'Friday, ' else '' end
+case when 64|freq_interval=freq_interval then 'Saturday,' else '' end)-1)
when freq_type=16 then 'On day '+cast(freq_interval as varchar(2))+' of the month'
else 'The '+
case freq_relative_interval when 1 then 'first '
when 2 then 'second '
when 4 then 'third '
when 8 then 'forth '
else 'last ' end
+'day of the month'
end as Interval,
case freq_subday_type when 1 then 'At ' + reverse(left(stuff('000000', 6-len(cast(active_start_time as varchar(6))), len(cast(active_start_time as varchar(6))), cast(active_start_time as varchar(6))), 2))+':'+
substring(reverse(stuff('000000', 6-len(cast(active_start_time as varchar(6))), len(cast(active_start_time as varchar(6))), cast(active_start_time as varchar(6)))), 3, 2)+':'+
substring(reverse(stuff('000000', 6-len(cast(active_start_time as varchar(6))), len(cast(active_start_time as varchar(6))), cast(active_start_time as varchar(6)))), 1, 2)
when 2 then 'Every '+cast(freq_subday_interval as varchar(1000))+' seconds'
when 4 then 'Every '+cast(freq_subday_interval as varchar(1000))+' mins'
else 'Every ' +cast(freq_subday_interval as varchar(1000))+' hours'
end as 'Daily execution (hhmmss)',
avg(run_duration) AvgDuration,
min(run_duration) MinDuration,
max(run_duration) MaxDuration

from sysjobs j
left outer join sysjobhistory h on j.job_id=h.job_id
left outer join sysjobschedules js on j.job_id=js.job_id
left outer join dbo.sysschedules s on js.schedule_id=s.schedule_id
group by
j.name,
s.name,
case freq_type when 1 then 'Once'
when 4 then 'Daily'
when 8 then 'Weekly'
when 16 then 'Monthly'
when 32 then 'Monthly Relative'
else 'On start' end,
case when freq_type=4 then
case when freq_interval=1 then 'Every day' else 'Every '+cast(freq_interval as varchar(2))+' days' end
when freq_type=8 then 'Every '+
left(case when 1|freq_interval=freq_interval then 'Sunday, ' else '' end
+case when 2|freq_interval=freq_interval then 'Monday, ' else '' end
+case when 4|freq_interval=freq_interval then 'Tuesday, ' else '' end
+case when 8|freq_interval=freq_interval then 'Wednesday, ' else '' end
+case when 16|freq_interval=freq_interval then 'Thursday, ' else '' end
+case when 32|freq_interval=freq_interval then 'Friday, ' else '' end
+case when 64|freq_interval=freq_interval then 'Saturday,' else '' end,
len(case when 1|freq_interval=freq_interval then 'Sunday, ' else '' end
+case when 2|freq_interval=freq_interval then 'Monday, ' else '' end
+case when 4|freq_interval=freq_interval then 'Tuesday, ' else '' end
+case when 8|freq_interval=freq_interval then 'Wednesday, ' else '' end
+case when 16|freq_interval=freq_interval then 'Thursday, ' else '' end
+case when 32|freq_interval=freq_interval then 'Friday, ' else '' end
+case when 64|freq_interval=freq_interval then 'Saturday,' else '' end)-1)
when freq_type=16 then 'On day '+cast(freq_interval as varchar(2))+' of the month'
else 'The '+
case freq_relative_interval when 1 then 'first '
when 2 then 'second '
when 4 then 'third '
when 8 then 'forth '
else 'last ' end
+'day of the month'
end,

case freq_subday_type when 1 then 'At ' + reverse(left(stuff('000000', 6-len(cast(active_start_time as varchar(6))), len(cast(active_start_time as varchar(6))), cast(active_start_time as varchar(6))), 2))+':'+
substring(reverse(stuff('000000', 6-len(cast(active_start_time as varchar(6))), len(cast(active_start_time as varchar(6))), cast(active_start_time as varchar(6)))), 3, 2)+':'+
substring(reverse(stuff('000000', 6-len(cast(active_start_time as varchar(6))), len(cast(active_start_time as varchar(6))), cast(active_start_time as varchar(6)))), 1, 2)
when 2 then 'Every '+cast(freq_subday_interval as varchar(1000))+' seconds'
when 4 then 'Every '+cast(freq_subday_interval as varchar(1000))+' mins'
else 'Every ' +cast(freq_subday_interval as varchar(1000))+' hours'
end

order by j.name