What are List Jobs, Schedules, and Next Scheduled Run Datetimes?
List Jobs, Schedules, and Next Scheduled Run Datetimes I'm trying to list Jobs, Schedules, and Next Scheduled Run Datetimes. However, I don't appear to be succeeding. sysjobactivity hasnext_scheduled_run_date, which is what I need, but I cannot figure out how to connect it to both the Job and Schedule. If there's a way other than through sysjobactivity, please guide me there.
SELECT sj.name AS jobName , ss.name AS scheduleName , sja.next_scheduled_run_date FROM msdb.dbo.sysjobs sj INNER JOIN msdb.dbo.sysjobactivity sja ON sja.job_id = sj.job_id INNER JOIN msdb.dbo.sysjobschedules sjs ON sjs.job_id = sja.job_id INNER JOIN msdb.dbo.sysschedules ss ON ss.schedule_id = sjs.schedule_id WHERE (sj.name LIKE 'xray%') AND (sja.next_scheduled_run_date > GETDATE())
I have this script in my toolbox for sql server scheduled job:
select S.name AS JobName, SS.name AS ScheduleName, CASE(SS.freq_type) WHEN 1 THEN 'Once' WHEN 4 THEN 'Daily' WHEN 8 THEN (case when (SS.freq_recurrence_factor > 1) then 'Every ' + convert(varchar(3),SS.freq_recurrence_factor) + ' Weeks' else 'Weekly' end) WHEN 16 THEN (case when (SS.freq_recurrence_factor > 1) then 'Every ' + convert(varchar(3),SS.freq_recurrence_factor) + ' Months' else 'Monthly' end) WHEN 32 THEN 'Every ' + convert(varchar(3),SS.freq_recurrence_factor) + ' Months' -- RELATIVE WHEN 64 THEN 'SQL Startup' WHEN 128 THEN 'SQL Idle' ELSE '??' END AS Frequency, CASE WHEN (freq_type = 1) then 'One time only' WHEN (freq_type = 4 and freq_interval = 1) then 'Every Day' WHEN (freq_type = 4 and freq_interval > 1) then 'Every ' + convert(varchar(10),freq_interval) + ' Days' WHEN (freq_type = 8) then (select 'Weekly Schedule' = MIN(D1+ D2+D3+D4+D5+D6+D7 ) from (select SS.schedule_id, freq_interval, 'D1' = CASE WHEN (freq_interval & 1 <> 0) then 'Sun ' ELSE '' END, 'D2' = CASE WHEN (freq_interval & 2 <> 0) then 'Mon ' ELSE '' END, 'D3' = CASE WHEN (freq_interval & 4 <> 0) then 'Tue ' ELSE '' END, 'D4' = CASE WHEN (freq_interval & 8 <> 0) then 'Wed ' ELSE '' END, 'D5' = CASE WHEN (freq_interval & 16 <> 0) then 'Thu ' ELSE '' END, 'D6' = CASE WHEN (freq_interval & 32 <> 0) then 'Fri ' ELSE '' END, 'D7' = CASE WHEN (freq_interval & 64 <> 0) then 'Sat ' ELSE '' END from msdb..sysschedules ss where freq_type = 8 ) as F where schedule_id = SJ.schedule_id ) WHEN (freq_type = 16) then 'Day ' + convert(varchar(2),freq_interval) WHEN (freq_type = 32) then (select freq_rel + WDAY from (select SS.schedule_id, 'freq_rel' = CASE(freq_relative_interval) WHEN 1 then 'First' WHEN 2 then 'Second' WHEN 4 then 'Third' WHEN 8 then 'Fourth' WHEN 16 then 'Last' ELSE '??' END, 'WDAY' = CASE (freq_interval) WHEN 1 then ' Sun' WHEN 2 then ' Mon' WHEN 3 then ' Tue' WHEN 4 then ' Wed' WHEN 5 then ' Thu' WHEN 6 then ' Fri' WHEN 7 then ' Sat' WHEN 8 then ' Day' WHEN 9 then ' Weekday' WHEN 10 then ' Weekend' ELSE '??' END from msdb..sysschedules SS where SS.freq_type = 32 ) as WS where WS.schedule_id = SS.schedule_id ) END AS Interval, CASE (freq_subday_type) WHEN 1 then left(stuff((stuff((replicate('0', 6 - len(active_start_time)))+ convert(varchar(6),active_start_time),3,0,':')),6,0,':'),8) WHEN 2 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' seconds' WHEN 4 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' minutes' WHEN 8 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' hours' ELSE '??' END AS [Time], CASE SJ.next_run_date WHEN 0 THEN cast('n/a' as char(10)) ELSE convert(char(10), convert(datetime, convert(char(8),SJ.next_run_date)),120) + ' ' + left(stuff((stuff((replicate('0', 6 - len(next_run_time)))+ convert(varchar(6),next_run_time),3,0,':')),6,0,':'),8) END AS NextRunTime from msdb.dbo.sysjobs S left join msdb.dbo.sysjobschedules SJ on S.job_id = SJ.job_id left join msdb.dbo.sysschedules SS on SS.schedule_id = SJ.schedule_id order by S.name