August 3, 2010 at 12:49 am
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.
August 3, 2010 at 1:26 am
Hi,
Check it sysjobs,sysjobschedules, sysschedules objects in msdb database.
With thanks & regards
Balaji.G
August 3, 2010 at 1:35 am
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
June 28, 2012 at 5:18 pm
Thanks for this script!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply