Custom Metrics are OK for low-volumes of Jobs and servers, but as each and every Custom Metric of this kind cites ONE and ONLY ONE Job, and the Alert must, therefore, be named coincident with the Metric, it is not a long term viable option.

I have used a technique SIMILAR to what Fiona describes but using an excessive DURATION and, while it does work, it's not something I look forward to maintaining/reproducing across instances/jobs.

Please consider, as Steve requested, ENHANCING SQL Monitor so there is a built-in Alert that one can pick the Job Name and state EITHER the duration(s) from the Start-time after which a Low, Medium or High Alert must be emitted, or the exact time that the Job must be complete by.

SQL Monitor Forum Post http://www.red-gate.com/MessageBoard/viewtopic.php?p=57950#57950 details the scenario and query.

Hi Steve

Thanks for your idea. This is exactly the type of thing we have introduced Custom Metrics for in SQL Monitor version 3.0 (http://www.red-gate.com/monitor).
The idea behind Custom Metrics is that you can measure things that are important to you.
To show you how it might work I have put together some T-SQL that would do the job. I'm sure it can be improved or be written in a nice way, but it should show the idea:

You would create a custom metric with the following T-SQL.
It will return 0 is a job hasn't started, if it's completed, or if it's running within the expected timescales. It will return 1 if it's still running and outside the expected timescales.

You would need to edit the T-SQL to contain the name of your job and the expected start and finish hours / minutes.

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

In this example the job is called "TestJob" and it starts at 17:00 (5pm) and is expected to be complete by 17:02 (5.02pm).

You would then add an alert on this job which would be raised if the value goes *above* 0 (I.e. it will alert when the job returns 1). This indicates it's gone over the expected time-scales.

I hope that gives you some idea of the types of things that can be done using custom metrics.

Best regards, Fiona

It would be helpful if for jobs you can specify a time in which they should be finished by and if they overun that time then an email alert is sent out to notify you.