Did you ever run into the situation where you had a job running into your production hours and you didn’t get a notification because the job didn’t actually fail?
I have experienced just that and here is a little script to get you a notification for a job which is running too long. Create this stored procedure in msdb then schedule it to run from SQL Server Agent. The Threshold for what is considered too long is a parameter. For me I check every two hours for a job which has been running longer then 60 minutes. Tailor it to work for your environment. Script was tested in SQL 2008 R1 Sp2
/************************************************************************************
– Proc Name : [[Proc_LongRunningJobs]]
– Description : This procedure will report Long Running SQL Agent Jobs
– Author : Mother of All Geeks
– exec [Proc_LongRunningJobs] 60,’MyMailProfile’, ‘mom@motherofallgeeks.com’
***********************************************************************************/
CREATE
PROCEDURE [dbo].[Proc_LongRunningJobs]
(
@Threshold int= 60
,
@MailProfile
varchar(100),
@recipients
varchar(255))
as
BEGIN
SET
NOCOUNT
ON
BEGIN
TRY
DECLARE
@Check varbinary (255
)
SELECTTOP 1 @Check = ja.job_idFROM msdb.dbo.sysjobactivity jaINNERJOIN sysjobs j on ja.job_id= j.job_idWHERE j.enabled=1 – and start_execution_date is not null and(stop_execution_date ISNULL)andDATEDIFF(MI,start_execution_date,GETDATE())isnotnulland(DATEDIFF(mi,start_execution_date,GETDATE())> @Threshold )and run_requested_date >CONVERT(date,getdate()-1)ORDERBY name – start_execution_date DESC
IF (@Check isnotnull)BEGINDECLARE @longRunning NVARCHAR(MAX)SET @longRunning =‘<Font><b>Long Running Jobs </b></font> <BR>’+N’<Table border =1 cellpadding=3>’+N’<th>SQLInstance</th>’+N’<th>JobName</th>’+N’<th>StartDateTime</th>’+N’<th>CurrentRunTimeinMin</th>’+N’</tr>’+CAST(
( SELECT
td
=isnull(@@SERVERNAME,”),”,
td
=isnull(name ,”),”,
td
=isnull(start_execution_date,”),”,
td
=isnull(DATEDIFF(MI,start_execution_date,GETDATE()),”),”FROM msdb.dbo.sysjobactivity jaINNERJOIN sysjobs j on ja.job_id= j.job_idWHERE j.enabled=1 – and start_execution_date is not null and(stop_execution_date ISNULL)andDATEDIFF(MI,start_execution_date,GETDATE())isnotnulland(DATEDIFF(mi,start_execution_date,GETDATE())> @Threshold )and run_requested_date >CONVERT(date,getdate()-1)and run_requested_date isnotnullORDERBY name – start_execution_date DESC
FORXMLPATH(‘tr’),TYPE)asNVARCHAR(max))+N’</table>’/*EMAIL THE ALERT*/
DECLARE @subject varchar(max), @body varchar(max)SET @subject =‘Long Running Job On: ‘+convert(varchar(25),@@servername)SET @body =isnull(@longRunning,”)exec msdb..sp_send_dbmail@profile_name = @MailProfile, @recipients = @recipients, @subject = @subject, @body_format =‘HTML’, @body = @body,@query_result_header = 0END
END
TRY
BEGIN
CATCH
DECLARE @ErrorMessageCatch varchar(max)SELECT @ErrorMessageCatch =‘ErrorNumber:’+isnull(convert(varchar(7),ERROR_NUMBER()),‘ ‘)+‘ ErrorSeverity:’+isnull(convert(varchar(7),ERROR_SEVERITY()),‘ ‘)+‘ ErrorState:’+isnull(convert(varchar(7),ERROR_STATE()),‘ ‘)+‘ Calling Procedure:’+isnull(CONVERT(VARCHAR(25),OBJECT_NAME(@@PROCID)),‘ ‘)+‘ ErrorProcedure:’+isnull(ERROR_PROCEDURE(),‘ ‘)+‘ ErrorLine:’+isnull(convert(varchar(7),ERROR_LINE()),‘ ‘)+‘ ErrorMessage:’+isnull(ERROR_MESSAGE(),‘ ‘)
SELECT @ErrorMessageCatchRAISERROR(@ErrorMessageCatch,16,1)
END
CATCH
END
–
GO
Posted by motherofallgeeks 
