Stop the over running SQL Server Agent job automatically using T – SQL based job

We setup a SQL server agent job to check if one of the intermittently over running (running forever) jobs is still running and if it is, then stop it and send email notification.

Here is the T-SQL code:

DECLARE @JOB_NAME nvarchar(MAX) = 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

EXEC msdb.dbo.sp_stop_job N’ABC_JOB_NAME’

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

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

END

Sometimes the sp_stop_job will show that the job has been stopped, though when you actually try to re-run it you will get error: Request to run job ABC (from User USERXYZ) refused because the job is already running from a request by User USERXYZ. [SQLSTATE 42000] (Error 22022).  The step failed.

Navigate to SQL Agent and script the job and save the file. Now delete the job and recreate it using the saved script. Viola! You are back in action.

 

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