In SQL Server How to create job and stored procedure to check and notify any long running jobs every 5 hours

You want the server to notify you that there is a job which is running for more than 5 hours.

Create a SQL server recurring job to run every 5 hours and run the following stored procedure:

create PROCEDURE [dbo].[NotifyLongRunningJobs]

AS
BEGIN
SET NOCOUNT ON;
Declare @jobcount int = 0;
Declare @message nvarchar (max);
Declare @joblist nvarchar (max);
SELECT @jobcount = count(distinct (j.name ))
FROM msdb.dbo.sysjobactivity a INNER JOIN msdb.dbo.sysjobs j
ON a.job_id =j.job_id
WHERE CONVERT(DATE,a.start_execution_date )=CONVERT(DATE,GETDATE ())
AND DATEDIFF(HH,start_execution_date,GETDATE())> 5
AND a.stop_execution_date IS NULL

select @jobcount

if @jobcount>0
Begin
–Create Email text
select @joblist = STUFF(
(

SELECT ‘ , ‘ + CONVERT(NVARCHAR(1000), j.name)
FROM msdb.dbo.sysjobactivity a INNER JOIN msdb.dbo.sysjobs j
ON a.job_id =j.job_id
WHERE CONVERT(DATE,a.start_execution_date )=CONVERT(DATE,GETDATE ())
AND DATEDIFF(HH,start_execution_date,GETDATE())> 5
AND a.stop_execution_date IS NULL

FOR xml path(”)

)
, 1
, 1
, ”)
select @message = N'<FONT face=”Calibri” size=”3″><H3>Long Running Job List</H3>’ +
‘<BR>’ +
@joblist +
‘<P></FONT>’;
–Send Email

EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘EMAIL PROFILE’,
@recipients = ‘abc@xyz.com’,
@subject = ‘Long Running Job List’,
@body = @message,
@body_format = ‘HTML’ ;

End
END

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s