View Summary of SQL Server Agent jobs

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:

  • dbo.sysjobhistory
  • dbo.sysjobs
  • dbo.sysjobschedules
  • dbo.sysschedules

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.

  1. I created the stored procedure in my database for Admin tasks, rather than msdb.
  2. 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') 
  3. 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.

Advertisements
This entry was posted in Database Administration. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s