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!