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:
|
1 2 3 4 5 6 7 8 9 10 11 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 |
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.