I needed to restart SQL Server Agent for a production server, but it was tough to see when a good time was to reboot it. Some jobs run every few minutes, some ran on the 2nd day of the month, some ran every weekday, some every day. It was a mess to look at the Job Monitor.
So I went to system tables. It quickly became evident it was going to complicated as well. There are many msdb tables related to jobs:
The information is cryptic especially in the sysschedules table. In a couple of cases, I found scripts on other blogs to query this information. But none of them included the duration of the jobs. Also, by the way, a third party tool would be great for looking at this, but I don’t know if I’ll ever work anywhere that uses those!
Here’s my solution:
First, I used this stored procedure to translate the cryptic dbo.sysschedules columns into plain English like “Every 1 week(s) on Sunday at 4:00” or “Every day every 30 minute(s) between 14:15 and 19:00”. There are a couple of changes I made to it.
- I created the stored procedure in my database for Admin tasks, rather than msdb.
- At the end of the stored procedure, I added this line:
SET @schedule_description = REPLACE(@schedule_description,'Every 1 week(s) on Monday, Tuesday, Wednesday, Thursday, Friday', 'Every weekday')
- I renamed it to [dbo].[msdb_job_schedule_description]
Second, I needed to get the duration to find the average and maximum duration of the jobs. Part of this required a function to convert the duration from HHMMSS format to seconds. So I joined the results of the stored procedure above to dbo.sysjobhistory. And here is the script that I came up with. To make sure I give credit, the part in the middle is also from this article. It’s just the small scripts surrounding it that I wrote, but hey the contribution needed to be made!
use msdb DECLARE @JobSchedule TABLE (job_id uniqueidentifier, name sysname, active_start_time varchar(30), ScheduleDesc varchar(200), IsEnabled bit ) INSERT @JobSchedule SELECT dbo.sysjobs.job_id, dbo.sysjobs.name, CAST(dbo.sysschedules.active_start_time / 10000 AS VARCHAR(10)) + ':' + RIGHT('00' + CAST(dbo.sysschedules.active_start_time % 10000 / 100 AS VARCHAR(10)), 2) AS active_start_time, admin.dbo.[msdb_job_schedule_description](dbo.sysschedules.freq_type, dbo.sysschedules.freq_interval, dbo.sysschedules.freq_subday_type, dbo.sysschedules.freq_subday_interval, dbo.sysschedules.freq_relative_interval, dbo.sysschedules.freq_recurrence_factor, dbo.sysschedules.active_start_date, dbo.sysschedules.active_end_date, dbo.sysschedules.active_start_time, dbo.sysschedules.active_end_time) AS ScheduleDscr, dbo.sysjobs.enabled FROM dbo.sysjobs INNER JOIN dbo.sysjobschedules ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id select JS.name,JS.active_start_time, JS.ScheduleDesc, CAST(max( Admin.dbo.ufn_JobIntToSeconds(JH.run_duration)) / 60.0 as numeric(10,0)) as MaxDurationMinutes, CAST( AVG( Admin.dbo.ufn_JobIntToSeconds(JH.run_duration)) / 60.0 as numeric(10,0)) as AvgDurationMinutes from @JobSchedule JS inner join dbo.sysjobhistory JH on JS.job_id = JH.job_id where JS.IsEnabled = 1 group by JS.name, JS.active_start_time, JS.ScheduleDesc having CAST( AVG( Admin.dbo.ufn_JobIntToSeconds(JH.run_duration)) / 60.0 as numeric(10,0)) > 0 order by active_start_time
Note that I inserted a line above that excludes jobs that run in less than a minute. This is based on the assumption that these jobs are not critical. You may want to take that line out the first time you run it.
This script does make it easier, but it is still hard to visualize a good time to restart SQL Server Agent. In Excel I mapped out when jobs were running, and I was able to figure it out. I’m sure this is where a 3rd party tool would help. But I hope this helps you.