SQL Server Agent Job Monitoring

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

Advertisements