Solved

SQL Agent Timeout

Posted on 2016-10-11
5
57 Views
Last Modified: 2016-10-12
Hello Experts Exchange
I have a SQL Agent job that has just ran and ran for its schedule run yesterday.

I want to have a new SQL Agent Job that checks how long the first job has ran for and stop it if it has ran for 90 minutes.

I have found some code on the internet, however I having some problems with it.

This is the Code.

DECLARE @JobToRun NVARCHAR(128) = 'My Agent Job'
DECLARE @dtStart DATETIME = GETDATE(), @dtCurr DATETIME
DECLARE @ExecutionStatus INT, @LastRunOutcome INT, @MaxTimeExceeded BIT = 0
DECLARE @TimeoutMinutes INT = 180 

EXEC msdb.dbo.sp_start_job @JobToRun
SET @dtCurr = GETDATE()
WHILE 1=1
BEGIN
	WAITFOR DELAY '00:00:10'
	SELECT @ExecutionStatus=current_execution_status, @LastRunOutcome=last_run_outcome 
	FROM OPENQUERY(LocalServer, 'set fmtonly off; exec msdb.dbo.sp_help_job') where [name] = @JobToRun
	IF @ExecutionStatus <> 4
	BEGIN -- job is running or finishing (not idle)
		SET @dtCurr=GETDATE()
		IF DATEDIFF(mi, @dtStart, @dtCurr) > @TimeoutMinutes
		BEGIN	
			EXEC msdb.dbo.sp_stop_job @job_name=@JobToRun					
			-- could log info, raise error, send email etc here
		END
		ELSE
		BEGIN
			CONTINUE
		END
	END
	IF @LastRunOutcome = 1  -- the job just finished with success flag
	BEGIN
		-- job succeeded, do whatever is needed here
		print 'job succeeded'									
	END

END

Open in new window



I keep getting error messages around these two lines.

SELECT @ExecutionStatus=current_execution_status, @LastRunOutcome=last_run_outcome
FROM OPENQUERY(LocalServer, 'set fmtonly off; exec msdb.dbo.sp_help_job') where [name] = @JobToRun

Does anyone have some code to do my requirements?

Regards

SQLSearcher
0
Comment
Question by:SQLSearcher
5 Comments
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41838189
I keep getting error messages around these two lines.
Ok, we already know WHERE is the error but WHAT is the error message?
1
 
LVL 42

Expert Comment

by:EugeneZ
ID: 41838284
self linked server " OPENQUERY(LocalServer," needs some extra steps to setup"
you do not need to use when connect the same server//

if you really need it
check if this was done to make it work
exec sp_serveroption @server = 'YOURSERVER'
   ,@optname = 'DATA ACCESS'
   ,@optvalue = 'TRUE'


--
see the code to check long running jobs

by Thomas Larock

 HOW TO: FIND CURRENTLY RUNNING LONG SQL AGENT JOBS
http://thomaslarock.com/2012/10/how-to-find-currently-running-long-sql-agent-jobs/
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 41838840
/*
Here's an alternative:

1) Create a separate job with the code below.
2) When the original job starts, have it immediately start this job.
*/

DECLARE @job_name nvarchar(128)
DECLARE @job_id uniqueidentifier

SET @job_name = 'My Agent Job'

--Wait 90 minutes ...
W_A_I_T_F_O_R D_E_L_A_Y '01:30:00' /*remove the underscores: filter blocks me from posting it otherwise*/

--... and if the original job is still running, stop it.
IF OBJECT_ID('tempdb.dbo.#job_info') IS NOT NULL
      DROP TABLE #job_info
CREATE TABLE #job_info (  
    job_id uniqueidentifier,  
    last_run_date int,  
    last_run_time int,  
    next_run_date int,  
    next_run_time int,  
    next_run_schedule_id int,  
    requested_to_run int,
    request_source int,  
    request_source_id varchar(100),  
    running int,  
    current_step int,  
    current_retry_attempt int,  
    state int
)

SELECT @job_id = j.job_id
FROM msdb.dbo.sysjobs j
WHERE j.name = @job_name

INSERT INTO #job_info
EXEC master.sys.xp_sqlagent_enum_jobs 1, 'irrelevant', @job_id

IF (SELECT MAX(running) FROM #job_info WHERE job_id = @job_id) > 0
BEGIN
    EXEC msdb.dbo.sp_stop_job @job_id = @job_id /*@job_name = @job_name*/
END /*IF*/
0
 

Author Closing Comment

by:SQLSearcher
ID: 41839990
Thank you for your help.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41840293
No problem.

I meant to also explicitly state how to start the other job; since a job start is asynchronous, the "check" job can be started and the main job then continue normally.

--in 'My Agent Job', Step1:
EXEC msdb.dbo.sp_start_job 'My Agent Job Stop If Runs Too Long'

...Step2...rest of code for 'My Agent Job'
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access sql to sql server express 10 32
Sql Stored Procedure field variable 17 27
What's wrong with this T-SQL Foreign Key? 7 41
MS SQL + Insert Into Table - If Doesnt Exist 9 32
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question