Mechanism to alert DBA when a SQL scheduled job runs for a long time in SQL2005

Hi all,
There is some problems with some of my SQL jobs related with netbackup strergy and applicational pakages as well, that run on sometime quite unusually a long time, so adversely they affect work.
Is there any inbuilt SQL mechanism ,by which we can get alerted , if the job runs abnormally for a longer time. No 3rd party tools, but any systems tables, or DMV's anything inbuilt that can suit this.
Appreciate if any of you have any queries by which abnormally long job duration can be monitored and have it alerted, might be we can use dbmail to alert, and the msdb job duration set can be queried, if any of you have any customated queries or resolutions, please do help me.
Thanks
Eben E

Hi,
Its SQL job that has netbackup step command's and it runs from a seperate netbackup server, the job duration was found to be around 4 hours and however the job usually complets in 1 hour.
I agree with the accesment part as the job was force run when we faced the issue, it did not run in its usual time. I will find the flow of data during that time from netbackup end
Since job shows as running status and ultimately the jobduration gets set in msdb table , but i do feel that it records the duration time only when it completes, if there is a possibility to alert the DBA that the job is running more than the usual time.
Thanks
Eben

I wrote my own stored procedure that I run hourly to check if I have any long running jobs and e-mails via DBMail. This is basically what I do:SET
@SQLStmt = ''SELECT @SQLStmt = 'SELECT @lCountJobs = count(*) FROM msdb..sysjobactivity
WHERE Datediff('
+ @szRunUnit + ',start_execution_date,getdate()) > ' + CONVERT(VARCHAR(10),@lRunValue)
+ ' and stop_Execution_date is null AND DATEDIFF (dd,start_execution_date,getdate()) < 2'
SET @ParmDefinition = N'@lCountJobs int OUT'PRINT @SQLStmt
EXECUTE sp_Executesql @SQLStmt,@ParmDefinition,@lCountJobs OUTIF @lCountJobs > 0
BEGINSELECT @SQLStmt = 'SELECT @szJobName = j.name, @dtStartExecDate = ja.start_execution_date
FROM msdb..sysjobs j join msdb..sysjobactivity ja
ON j.job_id = ja.job_id
WHERE Datediff(' + @szRunUnit + ',start_execution_date,getdate()) > '
+ CONVERT(VARCHAR(10),@lRunValue)
+ ' and stop_Execution_date is null AND DATEDIFF (dd,start_execution_date,getdate()) < 2'
SET @ParmDefinition = N'@szJobName nvarchar(128) OUT,@dtStartExecDate datetime OUT'
EXECUTE sp_Executesql @SQLStmt,@ParmDefinition,@szJobName OUT,@dtStartExecDate OUT
SELECT @vcSubject = @@SERVERNAME + '-' + @szJobName + ' has been running since ' + CONVERT(VARCHAR(35),@dtStartExecDate)SELECT @QUERY = 'SELECT j.name, j.enabled,j.description,ja.start_execution_date from msdb..sysjobs j join msdb..sysjobactivity ja
on j.job_id = ja.job_id
WHERE Datediff(' + @szRunUnit + ',start_execution_date,getdate()) > '
+ CONVERT(VARCHAR(5),@lRunValue)
+ ' and stop_Execution_date is null AND DATEDIFF (dd,start_execution_date,getdate()) < 2'PRINT @QUERY
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBA',@recipients
= @szEmailAddresses,@body = 'Long Running Jobs
'
,@query
= @QUERY,@subject
= @vcSubject

Hi Brooke,
I am not a good developer , i feel that the stored procedure to be very useful, but i get declaration erorrs , when i execute, can you help in fine tuning this script
There is an Execute statement in between, so does this revoke by itself using any PROC option (not able to find) as u configure it to run every hour.
Help me to have this statement complete
Messages:Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@SQLStmt".
Msg 137, Level 15, State 2, Line 4
Must declare the scalar variable "@szRunUnit".
Msg 137, Level 15, State 1, Line 8
Must declare the scalar variable "@ParmDefinition".
Msg 137, Level 15, State 2, Line 10

its quite late now to give a reply, was quite held in a lot of mess ... yeah we do have OEM to monitor MS SQL through plugin , but the tool is not so productive for Windows.....
will check that site for code....
thanks