• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 134
  • Last Modified:

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
0
SQLSearcher
Asked:
SQLSearcher
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I keep getting error messages around these two lines.
Ok, we already know WHERE is the error but WHAT is the error message?
1
 
Eugene ZCommented:
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
 
Scott PletcherSenior DBACommented:
/*
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
 
SQLSearcherAuthor Commented:
Thank you for your help.
0
 
Scott PletcherSenior DBACommented:
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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now