This blog post is brought to you by the awesomeness that is the SQL Server Twitter community. Contributions were made by several awesome Tweeples, including Denny Cherry, Kendra Little, Ken Simmons, and Magnus Ahlkvist, among others.
What started this is something you’ve all probably run into at one time or another. We’re currently having some resource contention on our server when a ton of processes kickoff and overlap in the morning. Now, I have a script that I’ve used in the past for monitoring SQL agent jobs, but this time I wanted to add some historical run-time information. I know the sysjobhistory table contains the information I need, but it has some… let’s say, creative ways of storing the data. Opinions on the reasons why vary:
Regardless the reason, I needed to do some conversions. Denny shared with us the msdb.dbo.agent_datetime function to convert run_date and run_time into an actual datetime value. I have to say, this certainly cleans up the code quite a bit! Then Magnus shared a method to convert run_duration into seconds, which he modified from a script on SQLServerCentral. I was able to use these two tidbits to update my old script. You can now run this script to get back a list of all enabled procs, a script that will disable them, a breakdown of the schedule, and a historical run-time average.
/* How many days do you want to include in your run-time average? Recent values tend to be more useful. */ Declare @daysToAverage smallint = 30; Declare @avgRunTime Table ( job_id uniqueidentifier , avgRunTime int ); /* We need to parse the schedule into something we can understand */ 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'; /* First, let's get our run-time average */ Insert Into @avgRunTime Select job_id , Avg((run_duration/10000) * 3600 + (run_duration/100%100)*60 + run_duration%100) As 'avgRunTime' /* convert HHMMSS to seconds */ From msdb.dbo.sysjobhistory Where step_id = 0 -- only grab our total run-time And run_status = 1 -- only grab successful executions And msdb.dbo.agent_datetime(run_date, run_time) >= DateAdd(day, -@daysToAverage, GetDate()) Group By job_id; /* Now let's get our schedule information */ 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 ) /* Finally, let's look at our actual jobs and tie it all together */ 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' , art.avgRunTime As 'avgRunTime_inSec' -- in seconds , (art.avgRunTime / 60) As 'avgRunTime_inMin' -- convert to minutes From msdb.dbo.sysjobs As job Join myCTE As sched On job.job_id = sched.job_id Left Join @avgRunTime As art On job.job_id = art.job_id Where job.enabled = 1 -- do not display disabled jobs Order By nextRunDate; |
If this doesn’t do exactly what you want, check out SQLJobVis, which Ken recommended. It’s a free tool that helps visualize the job history of SQL jobs.
That is easily the nicest query I’ve ever seen anyone write for MSDB. It’s sort of touching, really.
I get irritated every time I think about this topic. Thanks for the link to SQLJobVis – I always wonder why, since it is such a pain to query this data, why MSFT doesn’t have some adequate tools for visualizing job schedules in SSMS.
Any similar scripts to this that run under sql2005? Also any scripts similar to this that could pull jobs from a number of instances simultaneously so we can see jobs running on a cluster that could have multiple instances all running separate job schedules so we could see bottle necks that way?
Great query – Very helpful – Thanks so much.
@Jason: I could run the script against a SQL Srv 2K5 with only one minor fix right at the beginning:
Declare @daysToAverage smallint
set @daysToAverage = 30 ;
SQL 2K5 does not like Declare @daysToAverage smallint = 30
@Michelle Thank you again for an awesome script which now is in my repository
Regards
Dirk
i will like to work with any interested agent. i have about two ready screenplays.
Have you ever extended this script to show the output on job step level? Would like to be able to see which steps in the jobs are taking the longest on average.
just what i needed, thanks soooo much!
New to SQL and I’m very grateful that you took time to write this. Thank you!
Hi …Relly thanks for sharing such a nice Query….thanks a lot
It’s an awesome script and I have been using it for years now.
Just wondering if there is a way to identify the spid for each job ?
Thanks
Rajesh