Stop Jobs That Run Too Long

Since I seem to run across lots of questions about how to make sure that no jobs get in the way of ETL or other stressful data loads, I thought that I should post some ways you can stop jobs.

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-stop-job-transact-sql?view=sql-server-ver16

I have always done this by having a monitoring job that executes on a schedule that runs at a time when you need other jobs to stop.  Of course, you need to be aware that stopping jobs can come with unwanted side effects of some data change that may be unfinished (there may be a rollback) and the stopped job will have to gracefully be re-run at another time.  You will also see the stopped job as cancelled in the job activity monitor.  And, hopefully you are aware that you can tell a job to stop but if it is doing work using a linked server, it may not stop as expected or it can take a while if it is rolling back a transaction.

But let’s get down to business….Here’s the straightforward and simple way:

EXEC msdb.dbo.sp_stop_job  @job_name = ‘DBMaint.UpdateStatistics’

Stop Jobs Running Longer Than X Hours

But…..if you have to get creative and get a list of all jobs that are running and stop them it gets a bit more complicated.  Maybe you can’t have anything running after 3am because you have a nightly ETL process that starts then.  Simply run this code in your “Stop at 2:55AM” job and you are ready for the ETL to start.

DECLARE @MaxRunMin INT
DECLARE @JobName VARCHAR(250)

SET @MaxRunMin = 120  -- 2 hours

-- create temp table in case more than one job is found
IF (SELECT OBJECT_ID('tempdb..#JobsToStop')) IS NOT NULL
	DROP TABLE #JobsToStop
CREATE TABLE #JobsToStop 
	(JobName VARCHAR(250));

INSERT INTO #JobsToStop
SELECT sj.name
FROM msdb.dbo.sysjobactivity ja
JOIN msdb.dbo.sysjobs sj ON sj.job_id = ja.job_id
WHERE ja.stop_execution_date IS NULL -- job hasn't stopped running
	AND ja.start_execution_date IS NOT NULL -- job is currently running
	AND DATEDIFF(MINUTE,ja.start_execution_date,GETDATE()) > @MaxRunMin  -- job has been running longer than max mins
ORDER BY ja.start_execution_date

DECLARE joblist CURSOR FOR
SELECT JobName FROM #JobsToStop

OPEN joblist 
FETCH NEXT FROM joblist INTO @JobName
WHILE @@FETCH_STATUS=0
BEGIN
	EXEC msdb.dbo.sp_stop_job  @job_name = @JobName 
	PRINT 'Stopped ' + @JobName
	FETCH NEXT FROM joblist INTO @JobName 
END
CLOSE joblist
DEALLOCATE joblist

Stop Jobs by Job Category and Running Longer Than X Hours

I can take the above code and by adding some things (like category) I can get more specific on what jobs we stop.  Job categories are located in msdb.

select * from msdb.dbo.syscategories

There is a category for Database Maintenance and, as you can see, you can create your own custom categories if it makes job management easier.

EXEC msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N’SQLRX’

And now we can stop any Database Maintenance jobs that are currently running longer than 2 hours.

-- Check to see if Maintenance jobs are running too long and stop them
DECLARE @MaxRunMin INT
DECLARE @JobName VARCHAR(250)

SET @MaxRunMin = 120  -- 2 hours

-- create temp table in case more than one job is found
IF (SELECT OBJECT_ID('tempdb..#JobsToStop')) IS NOT NULL
	DROP TABLE #JobsToStop
CREATE TABLE #JobsToStop 
	(JobName VARCHAR(250));

INSERT INTO #JobsToStop
SELECT sj.name
FROM msdb.dbo.sysjobactivity ja
JOIN msdb.dbo.sysjobs sj ON sj.job_id = ja.job_id
JOIN msdb.dbo.syscategories sc ON sj.category_id = sc.category_id
WHERE ja.stop_execution_date IS NULL -- job hasn't stopped running
	AND ja.start_execution_date IS NOT NULL -- job is currently running
	AND DATEDIFF(MINUTE,ja.start_execution_date,GETDATE()) > @MaxRunMin  -- job has been running longer than max mins
	AND sc.name = 'Database Maintenance'  -- maintenance category
ORDER BY ja.start_execution_date

DECLARE joblist CURSOR FOR
SELECT JobName FROM #JobsToStop

OPEN joblist 
FETCH NEXT FROM joblist INTO @JobName
WHILE @@FETCH_STATUS=0
BEGIN
	EXEC msdb.dbo.sp_stop_job  @job_name = @JobName 
	PRINT 'Stopped ' + @JobName
	FETCH NEXT FROM joblist INTO @JobName 
END
CLOSE joblist
DEALLOCATE joblist

Stop Specific Jobs Running Longer Than X hours

To stop specific jobs from running after a specific time, I simply added a problem jobs variable that can be populated with a comma delimited list of jobs you already know don’t need to be running.

DECLARE @MaxRunMin INT
DECLARE @JobName VARCHAR(250)
DECLARE @ProblemJobs VARCHAR(MAX)

SET @MaxRunMin = 120  -- 2 hours
SET @ProblemJobs = 'DBMaint.UpdateStatistics,SQLRX - Defrag' -- comma delimited

-- create temp table in case more than one job is found
IF (SELECT OBJECT_ID('tempdb..#JobsToStop')) IS NOT NULL
	DROP TABLE #JobsToStop
CREATE TABLE #JobsToStop 
	(JobName VARCHAR(250));

INSERT INTO #JobsToStop
SELECT sj.name
FROM msdb.dbo.sysjobactivity ja
JOIN msdb.dbo.sysjobs sj ON sj.job_id = ja.job_id
JOIN msdb.dbo.syscategories sc ON sj.category_id = sc.category_id
WHERE ja.stop_execution_date IS NULL -- job hasn't stopped running
	AND ja.start_execution_date IS NOT NULL -- job is currently running
	AND DATEDIFF(MINUTE,ja.start_execution_date,GETDATE()) > @MaxRunMin  -- job has been running longer than max mins
	--AND sc.name = 'Database Maintenance'  -- maintenance category
	AND sj.name IN (SELECT * FROM STRING_SPLIT(@ProblemJobs,','))  --List of specific jobs to stop
ORDER BY ja.start_execution_date

DECLARE joblist CURSOR FOR
SELECT JobName FROM #JobsToStop

OPEN joblist 
FETCH NEXT FROM joblist INTO @JobName
WHILE @@FETCH_STATUS=0
BEGIN
	EXEC msdb.dbo.sp_stop_job  @job_name = @JobName 
	PRINT 'Stopped ' + @JobName
	FETCH NEXT FROM joblist INTO @JobName 
END
CLOSE joblist
DEALLOCATE joblist

Again, you put the code in a job that runs at whatever time you want these specific jobs to stop.

As you can see, there are many ways to customize this code to do what you want.  Hope this is helpful to someone!

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!