Restart a SQL Server Scheduled Job If Hanged

Introduction

Suppose there is a scenario where a SQL Server job gets hanged which is supposed to complete in 5 minutes but is running for the past 30 minutes.

Background

I encountered a scenario like this and it required the job to be restarted. But when you stop and start the job, it won't show you as stopped when you change its status to stopped from in execution.

Using the Code

There are 2 inbuilt SPs for job related tasks:

• sp_start_job: To start a job, and
• sp_stop_job: To stop a job

But when we use these one after the other, the job won't get restarted, in fact it says the job is still running. Table locking could be the reason for the same.

So the solution is to put a wait for command. To delay the execution of start by minimum 5 seconds.

USE msdb
GO

DECLARE @jobname varchar(30)
SET @jobname=’test 1'

IF NOT EXISTS(select 1 from sysjobhistory 
where job_id in (select job_id from sysjobs where name=@jobname)
and run_date>=substring(convert(varchar(10),getdate()-2,112),1,10)
and run_status=1)
BEGIN

IF EXISTS (select 1 from msdb.dbo.sysjobactivity AS sja
INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
WHERE sja.start_execution_date IS NOT NULL
AND sja.stop_execution_date IS NULL
AND datediff(minute,sja.start_execution_date,getdate())>1
AND name=@jobname
)
BEGIN
EXEC dbo.sp_stop_job @jobname
waitfor delay ’00:00:10'
END

IF NOT EXISTS(SELECT 1 FROM msdb.dbo.sysjobactivity AS sja
INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
WHERE sja.start_execution_date IS NOT NULL
AND sja.stop_execution_date IS NULL
AND name=@jobname)
BEGIN
EXEC sp_start_job @jobname

END
ELSE
RAISERROR (‘Job is in running state’, — Message text.
10, — Severity,
1, — State,
N’Test’)

END
ELSE
BEGIN
RAISERROR (‘Job has already been executed today’, — Message text.
10, — Severity,
1, — State,
N’job’)
END
GO 

Thanks!