Link to home
Start Free TrialLog in
Avatar of SQLSearcher
SQLSearcher

asked on

SQL Agent Timeout

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
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

I keep getting error messages around these two lines.
Ok, we already know WHERE is the error but WHAT is the error message?
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/
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of SQLSearcher
SQLSearcher

ASKER

Thank you for your help.
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'