Solved

SQL Agent Timeout

Posted on 2016-10-11
5
77 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 50

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 43

Expert Comment

by:Eugene Z
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

691 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