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’;
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 = @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=’email@example.com’,
@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.