Check automatically if the SQL server agent job is still running and send email notification

One of the SQL Server Agent jobs we maintain keeps running for ever and hence we decided to implement an automatic job with T-SQL which will check if the SQL server agent job is still running and send email notification.

Following is the T – SQL:

DECLARE @JOB_NAME SYSNAME = N’ABC_JOB_NAME’;

IF EXISTS(
select 1
from msdb.dbo.sysjobs_view job
inner join msdb.dbo.sysjobactivity activity on job.job_id = activity.job_id
where
activity.run_Requested_date is not null
and activity.stop_execution_date is null
and job.name = @JOB_NAME
)

BEGIN

declare @body1 nvarchar(MAX)
set @body1 = ‘Server :’+@@servername + ‘ABC_JOB_NAME job still running ‘

EXEC msdb.dbo.sp_send_dbmail @recipients=’abc@xyz.com’,
@profile_name = ‘EMAIL PROFILE’,
@subject = ‘ABC_JOB_NAME job still running’,
@body = @body1,
@body_format = ‘HTML’ ;

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