It happens that sometimes we need to monitor the SQL Server agent jobs and its schedule timings. This query monitors SQL Server Agent jobs and its schedule frequencies.
This will helpful for situations like to view basic info, any downtime, evaluation of no activity time for up-gradation process or backup process etc.
Query output columns descriptions are shown in below table.
Download query Mon_Sql_Server_Agent_Job here
(you need to change the extension from .key to .sql or you can open to text as well)
Version: 1.1
Date: 25-Sept-2015
Description:
Sometimes we need to amend schedules as per our requirement, some days before I had to put multi scheduling ranges for single job process, which runs every 5 minutes, due to some very heavy bulk batch activity. This forced me to change my query to display, the start and end date & time of every job schedules so we can monitor gaps in between.
Download new query: Mon_Sql_Server_Agent_Job_V_1.1
| Column Name | Description | Remarks |
| name | SQL Server agent job name | |
| Owner Name | Owen’s Name | Some Windows Service group accounts are not appearing |
| description | Description of Agent Job | |
| isEnable | Enabled/ Disabled | |
| Frequency Type | · One Time Only · Daily · Weekly · Monthly · Monthly, Relative to freq_Interval · Runs when the SQL Server Agent service starts · Runs when the computer is idle |
|
| Frequency Interval | Unused Every n days Weekdays name | |
| Frequency Sub Type | If the frequency occurs within same day then the further details will display here e.g. Every 15 Minutes. | |
| Next Run Date | Next job running day | |
| Next Run Time | Next job running time | |
| active_start_time | Schedule start time | |
| active_end_time | Schedule end time | |
| active_start_date | Schedule start date | |
| active_end_date | Schedule end date | |
| Sch Version | Change version of schedule |
References:
MSDN:
https://msdn.microsoft.com/en-us/library/ms178644.aspx
https://msdn.microsoft.com/en-us/library/ms189817.aspx
https://msdn.microsoft.com/en-us/library/ms178593.aspx
Datetime conversion:
http://stackoverflow.com/questions/12568408/how-to-convert-an-integer-time-to-hhmmss00-in-sql-server-2008
