Recently, I wanted to find a list of all SQL Agent Jobs running on various servers. I was able to view this in SSMS, of course, but I wanted to be able to copy/paste and toss this into a spreadsheet. So instead of using SSMS, I wrote the script below, with significant help from the sysschedules entry in BOL, to show me the same information, using T-SQL. I also include a script to disable the job, because that’s just how I roll.
Declare @weekDay Table ( mask int , maskValue varchar(32) ); Insert Into @weekDay Select 1, 'Sunday' Union All Select 2, 'Monday' Union All Select 4, 'Tuesday' Union All Select 8, 'Wednesday' Union All Select 16, 'Thursday' Union All Select 32, 'Friday' Union All Select 64, 'Saturday'; With myCTE As( Select sched.name As 'scheduleName' , sched.schedule_id , jobsched.job_id , Case When sched.freq_type = 1 Then 'Once' When sched.freq_type = 4 And sched.freq_interval = 1 Then 'Daily' When sched.freq_type = 4 Then 'Every ' + Cast(sched.freq_interval As varchar(5)) + ' days' When sched.freq_type = 8 Then Replace( Replace( Replace(( Select maskValue From @weekDay As x Where sched.freq_interval & x.mask <> 0 Order By mask For XML Raw) , '"/><row maskValue="', ', '), '<row maskValue="', ''), '"/>', '') + Case When sched.freq_recurrence_factor <> 0 And sched.freq_recurrence_factor = 1 Then '; weekly' When sched.freq_recurrence_factor <> 0 Then '; every ' + Cast(sched.freq_recurrence_factor As varchar(10)) + ' weeks' End When sched.freq_type = 16 Then 'On day ' + Cast(sched.freq_interval As varchar(10)) + ' of every ' + Cast(sched.freq_recurrence_factor As varchar(10)) + ' months' When sched.freq_type = 32 Then Case When sched.freq_relative_interval = 1 Then 'First' When sched.freq_relative_interval = 2 Then 'Second' When sched.freq_relative_interval = 4 Then 'Third' When sched.freq_relative_interval = 8 Then 'Fourth' When sched.freq_relative_interval = 16 Then 'Last' End + Case When sched.freq_interval = 1 Then ' Sunday' When sched.freq_interval = 2 Then ' Monday' When sched.freq_interval = 3 Then ' Tuesday' When sched.freq_interval = 4 Then ' Wednesday' When sched.freq_interval = 5 Then ' Thursday' When sched.freq_interval = 6 Then ' Friday' When sched.freq_interval = 7 Then ' Saturday' When sched.freq_interval = 8 Then ' Day' When sched.freq_interval = 9 Then ' Weekday' When sched.freq_interval = 10 Then ' Weekend' End + Case When sched.freq_recurrence_factor <> 0 And sched.freq_recurrence_factor = 1 Then '; monthly' When sched.freq_recurrence_factor <> 0 Then '; every ' + Cast(sched.freq_recurrence_factor As varchar(10)) + ' months' End When sched.freq_type = 64 Then 'StartUp' When sched.freq_type = 128 Then 'Idle' End As 'frequency' , IsNull('Every ' + Cast(sched.freq_subday_interval As varchar(10)) + Case When sched.freq_subday_type = 2 Then ' seconds' When sched.freq_subday_type = 4 Then ' minutes' When sched.freq_subday_type = 8 Then ' hours' End, 'Once') As 'subFrequency' , Replicate('0', 6 - Len(sched.active_start_time)) + Cast(sched.active_start_time As varchar(6)) As 'startTime' , Replicate('0', 6 - Len(sched.active_end_time)) + Cast(sched.active_end_time As varchar(6)) As 'endTime' , Replicate('0', 6 - Len(jobsched.next_run_time)) + Cast(jobsched.next_run_time As varchar(6)) As 'nextRunTime' , Cast(jobsched.next_run_date As char(8)) As 'nextRunDate' From msdb.dbo.sysschedules As sched Join msdb.dbo.sysjobschedules As jobsched On sched.schedule_id = jobsched.schedule_id Where sched.enabled = 1 ) Select job.name As 'jobName' , sched.scheduleName , sched.frequency , sched.subFrequency , SubString(sched.startTime, 1, 2) + ':' + SubString(sched.startTime, 3, 2) + ' - ' + SubString(sched.endTime, 1, 2) + ':' + SubString(sched.endTime, 3, 2) As 'scheduleTime' -- HH:MM , SubString(sched.nextRunDate, 1, 4) + '/' + SubString(sched.nextRunDate, 5, 2) + '/' + SubString(sched.nextRunDate, 7, 2) + ' ' + SubString(sched.nextRunTime, 1, 2) + ':' + SubString(sched.nextRunTime, 3, 2) As 'nextRunDate' /* Note: the sysjobschedules table refreshes every 20 min, so nextRunDate may be out of date */ , 'Execute msdb.dbo.sp_update_job @job_id = ''' + Cast(job.job_id As char(36)) + ''', @enabled = 0;' As 'disableScript' From msdb.dbo.sysjobs As job Join myCTE As sched On job.job_id = sched.job_id Where job.enabled = 1 -- do not display disabled jobs Order By nextRunDate; |
Not sure what I’m doing with the @weekDay table? Then check out my post on bitwise operations in T-SQL.
Happy Coding!
Michelle Ufford (aka SQLFool)
PS: I haven’t tested this with every possible setting, just the ones I use. If I missed something, please let me know so I can correct it.
PPS: the sysjobschedules table only refreshes every 20 min, so the nextRunDate value may be a little out of date.
Source: http://sqlfool.com/2009/02/view-sql-agent-jobs-tsql/
Thanks, I need to do exactly this next week you’ve saved me a tonne of time!
You’re welcome, I’m glad I could help! 🙂
Nice. Do you know about the Central Management Server feature and group execute in SQL 2008? That way, you can run it once against all of your servers, and get the results back in a single results grid. Very handy for copy/pasting into a spreadsheet.
Great query. Just what I needed for easy documenting the scheduled jobs.
Regards
STefan
Pingback: sql server那点事 | an vimmer,one linuxer and a rider
Pingback: SQL Agent Job Script « SQL Fool
Could you tell me, what is reason to disabled jobs ? Reason to use this script ?
New Jr DBA
@Kahn I typically disable jobs whenever we’re about to perform a hardware upgrade or patching.
Nice script. I can use this for customers which need a lot of hand holding to get information about jobs. Now, I need a script to get information on the details/steps of each job.
Thanks.
I noticed that it doesn’t show all the enabled jobs on my server. Why is that?
I love this script, but I notice that it seems to leave out some jobs that are scheduled and enabled. These jobs show up with “select * From msdb.dbo.sysjobs” and are enabled. I’ve been looking through the script but can’t determine where the problem is.
Wonderful script Michelle. Thank you!
The below code might be helfful for you
select LEFT(RIGHT(‘000000’ + CONVERT(VARCHAR,s.active_start_time),6),2) + ‘:’ + SUBSTRING(RIGHT(‘000000’ + CONVERT(VARCHAR,s.active_start_time),6),3,2) + ‘:’ +
RIGHT(RIGHT(‘000000′ + CONVERT(VARCHAR,s.active_start_time),6),2) from msdb.dbo.sysschedules s inner join msdb.dbo.sysjobschedules j on j.schedule_id = s.schedule_id
inner join msdb.dbo.sysjobs sj on sj.job_id=j.job_id and sj.name=’@@Import BI and Process BI_SSAS’
Thank you Michelle for the script
Pingback: Job Schedule view | XL-UAT