Based on http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=AgentLongRunning&referringTitle=Home

Here is a job you can run to monitor for long running agent jobs:-

First it requires the function

Function udf_SysJobs_GetProcessid

CREATE FUNCTION dbo.udf_SysJobs_GetProcessid(@job_id uniqueidentifier)

RETURNS VARCHAR(8)

AS

BEGIN

RETURN (substring(left(@job_id,8),7,2) +

substring(left(@job_id,8),5,2) +

substring(left(@job_id,8),3,2) +

substring(left(@job_id,8),1,2))

END

Then schedule the following:-

/* ------------------------------------------------------------------------------------------------------

Purpose: To alert on long running jobs

Author: Carolyn Richardson

Date: 26th November 2008

Requires: Function udf_SysJobs_GetProcessid

------------------------------------------------------------------------------------------------------*/

DECLARE @MaxMinutes INT

DECLARE @JobQuery VARCHAR(max)

DECLARE @Count INT

DECLARE @SQLEmailProfile VARCHAR(250)

DECLARE @MsgSubject VARCHAR(250)

--Alter following variables

SET @MaxMinutes = 15

SET @MsgSubject ='Add your server name here Query running for more than '

+CAST(@MaxMinutes ASVARCHAR(5))+' mins'

SELECT p.spid,

j.name,

p.program_name,

isnull(DATEDIFF(mi, p.last_batch,getdate()), 0) [MinutesRunning],

last_batch

FROM master..sysprocesses p

JOIN msdb..sysjobs j ON dbo.udf_sysjobs_getprocessid(j.job_id)=substring(p.program_name, 32, 8)

WHEREprogram_namelike'SQLAgent - TSQL JobStep (Job %'

ANDisnull(DATEDIFF(mi, p.last_batch,getdate()), 0)> @MaxMinutes

SET @Count =@@ROWCOUNT

IF @Count >= 1

BEGIN --Get the Query SET @JobQuery ='SELECT p.spid,

j.name,

p.program_name,

isnull(DATEDIFF(mi, p.last_batch, getdate()), 0) [MinutesRunning],

last_batch

FROM master..sysprocesses p

JOIN msdb..sysjobs j ON dbo.udf_sysjobs_getprocessid(j.job_id) = substring(p.program_name,32,8)

WHERE program_name like ''SQLAgent - TSQL JobStep (Job %''

AND isnull(DATEDIFF(mi, p.last_batch, getdate()), 0) > '

+CAST(@MaxMinutes ASVARCHAR(5)) -- Get The mail Profile

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Add you email profile here',

@recipients =' Add your email address here',

@query = @JobQuery,

@subject = @MsgSubject, @attach_query_result_as_file = 1 ;

END