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:


select 1
from msdb.dbo.sysjobs_view job
inner join msdb.dbo.sysjobactivity activity on job.job_id = activity.job_id
activity.run_Requested_date is not null
and activity.stop_execution_date is null
and = @JOB_NAME


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=’’,
@profile_name = ‘EMAIL PROFILE’,
@subject = ‘ABC_JOB_NAME job stopped’,
@body = @body1,
@body_format = ‘HTML’ ;


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.



Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s