Solved

SQL Agent Timeout

Posted on 2016-10-11
5
70 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 49

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: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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

730 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