Click here to Skip to main content
12,296,023 members (58,550 online)
Click here to Skip to main content
Add your own
alternative version

Stats

4.5K views
2 bookmarked
Posted

Restart a SQL Server Scheduled Job If Hanged

, 15 Jul 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
Restart hanged SQL Server scheduled job

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!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

anki.mathur
Software Developer (Senior)
India India
No Biography provided

You may also be interested in...

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160525.2 | Last Updated 16 Jul 2014
Article Copyright 2014 by anki.mathur
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid