08 May 2013

Job overran

by SQL Monitor Team

12,107 0 Performance

This metric tells you whether a specific SQL Agent job that runs at exactly the same time every day has overrun its expected duration.

Install metric...

Metrics install automatically if you have Redgate Monitor installed.

If you are using Redgate’s SQL Server monitoring tool, Redgate Monitor, you can instantly install and run this metric on your servers.

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.

Metric definition

Name

'TestJob' overran

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.

The T-SQL query that will collect data


		
			
			
Transact-SQL
			
			-- 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 
) 
			
				
					
				
					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
				
						-- 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 
)

Instances to collect from

Select all

Databases to collect from

msdb

Collection frequency

Use collected or calculated values

Leave the Use a calculated rate of change between collections check box unchecked

Metric collection

Enabled

Alert definition

Alert name

'TestJob' overran

Description

The job being monitored ran for longer than expected.

Raise an alert when the metric value goes

Above the defined threshholds

Default threshold values

High:	Leave unchecked
Medium:	0
Low:	Leave unchecked

Raise an alert when the threshold is passed for

1 collection

Alert is

Enabled

12,107 Rate

Click to rate this post!

[Total: 1 Average: 5]

Job overran

Metric definition

Name

Description

The T-SQL query that will collect data

Instances to collect from

Databases to collect from

Collection frequency

Use collected or calculated values

Metric collection

Alert definition

Alert name

Description

Raise an alert when the metric value goes

Default threshold values

Raise an alert when the threshold is passed for

Alert is

What are custom metrics?

What is Redgate Monitor?

Featured custom metrics

Total database file size

Number of active backups and restores

Full backup to odd path

Find metrics