Solved

SQL Agent Timeout

Posted on 2016-10-11
5
64 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 48

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

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.
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…
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
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

856 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