Article By:

Frequent monitoring of server status is a critical DBA task. This is compounded when there are multiple servers running many jobs through the SQL Server Agent. Recently we were faced with assessing the job load on multiple servers, particularly the number and frequency of these jobs.
SQL Server exposes virtually all of this information and it can be easily accessed through queries. My first humble attempt looked like this:

USE [msdb]
GO
SELECT j.name,
js.step_id,
js.step_name,
js.subsystem,
CASE WHEN j.enabled = 1 THEN 'Yes' ELSE 'No' END enabled
FROM dbo.sysjobs j
JOIN dbo.sysjobsteps js ON js.job_id = j.job_id
JOIN master.dbo.sysservers s ON s.srvid = j.originating_server_id

This is the result:

Name Step Step Name Type Enabled
Update DW 1 Create Snapshot TSQL Yes
Backup Transaction Logs 1 Backup Transaction Logs SSIS Yes

So far I know which jobs are currently running. What is missing is how often each job is scheduled to run. These servers have been operational for years. During that time new jobs are created without considering the load already placed on the server.
The schedule dialog for jobs in SQL Server Management Studio is very straightforward. This is not the case for the SQL required to return the same information. There are several joins required to access the schedule data and there are dependent columns that require manipulation. The Microsoft documentation for dbo.sysschedules clearly defines these columns.
This is the information I need to retrieve:

  • Interval: Monthly, Weekly, Daily
  • Frequency: every x days, hours, minutes
  • Start time
  • End time

Surprisingly, the start and end times were a lot more difficult to decipher than the rest. Both are stored as integer values in the ‘HHMMSS’ format. If this was a string it would be simple to parse as the value would always have a length of six characters. Integers do not store leading zeroes and therefore 2:35 am is represented as ‘23500’ and midnight is simply ‘0’.
I did find a post here when searching on ‘active_start_time’ but it a) did not handle all permutations and b) everything was returned in 24 instead of 12 hour format. By implementing a few nested CASE statements I was able to return exactly the information we needed.

USE [msdb]
GO
SELECT j.name,
 js.step_id,
 js.step_name,
 js.subsystem,
 js.command,
 CASE
 WHEN j.enabled = 1 THEN 'Yes'
 ELSE 'No'
 END enabled,
 CASE
 WHEN ss.freq_type = 4 THEN
 CASE
 WHEN ss.freq_interval = 1 THEN
 CASE
 WHEN ss.freq_subday_type = 1 THEN 'Once daily'
 WHEN ss.freq_subday_type = 2 THEN 'Every ' + CAST(ss.freq_subday_interval AS varchar(2)) + ' seconds(s)'
 WHEN ss.freq_subday_type = 4 THEN 'Every ' + CAST(ss.freq_subday_interval AS varchar(2)) + ' minute(s)'
 WHEN ss.freq_subday_type = 8 THEN 'Every ' + CAST(ss.freq_subday_interval AS varchar(2)) + ' hours(s)'
 END
 ELSE 'Every ' + CAST(ss.freq_type AS varchar(2)) + ' days'
 END
 WHEN ss.freq_type = 8 THEN 'Weekly'
 WHEN ss.freq_type = 16 THEN 'Monthly'
 ELSE CAST(ss.freq_type AS varchar(3))
 END frequency,
 CASE
 WHEN LEN(CAST(ss.active_start_time as varchar)) BETWEEN 5 AND 6 THEN
 CASE
 WHEN ss.active_start_time < 120000 THEN
 LEFT(RIGHT('0' + CAST(ss.active_start_time as varchar), 6), 2) + ':' +
 SUBSTRING(RIGHT('0' + CAST(ss.active_start_time as varchar), 6), 3,2) + ':' +
 RIGHT(CAST(active_start_time as varchar), 2) + ' AM'
 ELSE
 LEFT(RIGHT('0' + CAST(ss.active_start_time as varchar), 6), 2) + ':' +
 SUBSTRING(RIGHT('0' + CAST(ss.active_start_time as varchar), 6), 3,2) + ':' +
 RIGHT(CAST(active_start_time as varchar), 2) + ' PM'
 END
 ELSE
 LEFT(RIGHT('0' + CAST(ss.active_start_time+120000 as varchar), 6), 2) + ':' +
 SUBSTRING(RIGHT('0' + CAST(ss.active_start_time+120000 as varchar), 6), 3,2) + ':' +
 RIGHT(CAST(active_start_time+120000 as varchar), 2) + ' AM'
 END [Start Time],
 CASE
 WHEN LEN(CAST(ss.active_end_time as varchar)) BETWEEN 5 AND 6 THEN
 CASE
 WHEN ss.active_end_time < 120000 THEN
 LEFT(RIGHT('0' + CAST(ss.active_end_time as varchar), 6), 2) + ':' +
 SUBSTRING(RIGHT('0' + CAST(ss.active_end_time as varchar), 6), 3,2) + ':' +
 RIGHT(CAST(active_end_time as varchar), 2) + ' AM'
 ELSE
 LEFT(RIGHT('0' + CAST(ss.active_end_time-120000 as varchar), 6), 2) + ':' +
 SUBSTRING(RIGHT('0' + CAST(ss.active_end_time as varchar), 6), 3,2) + ':' +
 RIGHT(CAST(active_end_time as varchar), 2) + ' PM'
 END
 ELSE
 LEFT(RIGHT('0' + CAST(ss.active_end_time+120000 as varchar), 6), 2) + ':' +
 SUBSTRING(RIGHT('0' + CAST(ss.active_end_time+120000 as varchar), 6), 3,2) + ':' +
 RIGHT(CAST(active_end_time+120000 as varchar), 2) + ' AM'
 END [End Time]
 FROM dbo.sysjobs j
JOIN dbo.sysjobsteps js ON js.job_id = j.job_id
JOIN dbo.sysjobschedules sj ON sj.job_id = j.job_id
JOIN dbo.sysschedules ss ON ss.schedule_id = sj.schedule_id
JOIN master.dbo.sysservers s ON s.srvid = j.originating_server_id

This is the result:

Name Step Step Name Type Enabled Frequency Start Time End Time
Update DW 1 Create Snapshot TSQL Yes Every 15 minutes(s) 12:00:00 AM 11:59:59 PM
 Backup Transaction Logs 1 Backup Transaction Logs SSIS  Yes Once daily 02:30:00 AM 11:59:59 PM

 Handling Multiple Servers

This script is fine for a single server. In our case we need to monitor approximately 40 servers. How can this be automated? There are several approaches and the one I feel is by far the most scalable and customizable uses SSIS. The post entitled Centralizing Data Collecting provides step-by-step instructions on how to create an SSIS project and log files. It literally took me 10 minutes and worked flawlessly the first time.

Need Assistance?

Speak with an Engineer

Not sure where to start? We're here to help walk you through the process, understand your environment, and provide the guidance you need to achieve cybersecurity maturity. Get in touch today.

Get in Touch