Job overran

| 0 comments | 7,358 views
Categories:

VN:F [1.9.17_1161]
Rating: 5.0/5 (1 vote cast)

This metric tells you if a specific SQL Server Agent job that runs at the same time every day has overrun. The metric is useful when a job can have negative effects on other processes if it overruns.

For example, you may have a series of consecutive jobs that run for 1 minute each, starting at 9am. It’s important for the first job to finish by 9:01, so the second one can begin, and so on. If the job runs for longer than expected, an alert is raised.

Important: You’ll need to edit the T-SQL with the job’s name and the expected start and finish hour and minute. In this query, the job is called ‘TestJob’, it starts at 17:00, and it should finish by 17:02.

Job overran, 5.0 out of 5 based on 1 rating

Metric definition

Metric Name:
'TestJob' overran
Note: Replace 'TestJob' with the name of the job you're monitoring.
Description:

This checks if a specific job has not finished by the expected time.

  • If 0 is returned, the job either:
  • - hasn't started,
  • - is running during the expected time, or
  • - has finished running.
  • If 1 is returned, the job is running beyond the expected finish time.
Enter the T-SQL query that will collect data:
-- Code to examine if a job has overrun to a specific time 
DECLARE @todaysdate DATETIME, @job_name NVARCHAR(50), 
@expected_start_hour INT, @expected_start_minute INT, 
@expected_finish_hour INT, @expected_finish_minute INT 

SELECT @todaysdate = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())), 
-- ****************************************************** 
-- FILL IN DETAILS SPECIFIC TO YOUR JOB 
-- ****************************************************** 
@job_name = 'TestJob', 
@expected_start_hour = 17, 
@expected_start_minute = 0, 
@expected_finish_hour = 17, 
@expected_finish_minute = 2 
-- Expected outcomes 
-- 0=job in expected timescales 
-- 1=job has overrun 

SELECT COUNT(*) AS JobOverRunning 
FROM msdb..sysjobs j 
WHERE 
j.name = @job_name 
AND EXISTS 
( 
SELECT TOP 1 * 
FROM msdb..sysjobsteps sj 
INNER JOIN msdb..sysjobactivity ja ON ja.job_id = j.job_id 
WHERE sj.job_id = j.job_id 
AND ja.start_execution_date >= DATEADD(mi, @expected_start_minute, DATEADD(hh, @expected_start_hour, @todaysdate)) 
AND ja.stop_execution_date IS NULL -- only include stuff that is still running 
AND GETDATE() >= DATEADD(mi, @expected_finish_minute, DATEADD(hh, @expected_finish_hour, @todaysdate)) 
ORDER BY start_execution_date 
) 
Note: Replace 'TestJob' with the name of the job you're monitoring, and change the start and finish hour and minute accordingly.
Select instances to collect from:
Select all
Choose databases to collect from:
Specify databases and type
msdb
Set collection frequency:
Collect data every minute
Use collected or calculated values:
Leave the Use a calculated rate of change between collections check box turned off
Metric collection is:
Enabled

Alert definition

Alert name:
'TestJob' overran
Note: Replace 'TestJob' with the name of the job you're monitoring.
Description:
The job being monitored ran for longer than expected.
Raise an alert when the metric value goes:
Above the defined thresholds
Default threshold values:
Set the thresholds as follows:
High:Leave unchecked
Medium:0
Low:Leave unchecked
Raise an alert when the threshold is passed for:
Enter 1 collection
This alert is:
Enabled
VN:F [1.9.17_1161]
Rating: 5.0/5 (1 vote cast)