October 30, 2012 at 4:09 am
Need a query which gets me the list of jobs which is scheduled to run on a particular day and its scheduled run time. I need the schedule even if the job is disabled, but schedule enabled.
I dont want to create a function. A query without creation of a stored procedure or function would be helpful.
October 30, 2012 at 4:23 am
In the simplest form, with no filters:
SELECTsj.[name],
sjs.next_run_date, sjs.next_run_time
FROM MSDB..sysjobs sj
JOIN MSDB..sysjobschedules sjs ON sj.job_id = sjs.job_id
You can use MSDB..sysjobs, MSDB..sysjobschedules, MSDB..sysjobhistory, MSDB..sysjobservers, MSDB..sysjobactivity to JOIN and filter your data using WHERE clauses, and order the data.
You can use the 'enabled' column in sysschedules too if you want to filter on schedule enabled and ignore the 'enabled' column in sysjobs.
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
October 30, 2012 at 4:35 am
Query looks great. But, it missed out one detail. If the job is scheduled to run daily, then it gives the next run only and does not give the day after tomorrow's run date/time.
If job runs daily, and i run the query on wednesday, it gives thursday date/time, but not fri,sat,sun dates. If i want to see what jobs run on sunday, how to include that in this (or any other) query.
October 30, 2012 at 4:48 am
Ah, I see. msdb..sysschedules will be your friend then, it defines freq_interval, freq_type, etc. and you should be able to join on schedule_id with msdb..sysjobschedules as a filter.
Unfortunately I don't have time to put together the full query for you, but it's an interesting thing to do that I'll try and get around to - always nice to have something new for my code dump every day!
I would build this as an SP though - you can then feed in a date or range to get filtered info out.
If you wanted a quick view on jobs and schedules that you can eyeball, have you considered using Red Gate Backup to view and order the jobs?
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
October 30, 2012 at 4:58 am
Oh oh... then i would be waiting for the script with bated breath 🙁
October 30, 2012 at 6:05 am
In the meantime anyone out there who can help with the qury
October 30, 2012 at 7:24 am
balasach82 (10/30/2012)
In the meantime anyone out there who can help with the qury
deifinetely but why dont you give a try and post what exactly are you expecting in terms of column details..
Regards
Durai Nagarajan
October 30, 2012 at 7:36 am
check if this helps
exec msdb.dbo.sp_help_job
Regards
Durai Nagarajan
October 30, 2012 at 9:14 am
PMed you with rough but tested script.
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
October 31, 2012 at 7:18 am
derek.colley (10/30/2012)
PMed you with rough but tested script.
why not put it on forum for others to see.
Regards
Durai Nagarajan
October 31, 2012 at 12:27 pm
balasach82 (10/30/2012)
Need a query which gets me the list of jobs which is scheduled to run on a particular day and its scheduled run time. I need the schedule even if the job is disabled, but schedule enabled.I dont want to create a function. A query without creation of a stored procedure or function would be helpful.
Below will help you, I use this and have it in my script repository... no need of creating function or anything like that ... you can create an SP as well :
---------------------------------------------------------------------------------------------------
-- Date Created: September 21, 2006
-- Author: William McEvoy
--
-- Description: This procedure produces a report that details the schedule information for all
-- scheduled jobs on the server.
--
---------------------------------------------------------------------------------------------------
set nocount on
select 'Server' = left(@@ServerName,20),
'JobName' = left(S.name,30),
'ScheduleName' = left(ss.name,25),
'Enabled' = CASE (S.enabled)
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE '??'
END,
'Frequency' = 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,
'Interval' = 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' = 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,
'Time' = 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,
'Next Run 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
from msdb.dbo.sysjobschedules SJ
join msdb.dbo.sysjobs S on S.job_id = SJ.job_id
join msdb.dbo.sysschedules SS on ss.schedule_id = sj.schedule_id
order by S.name
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply