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.
I keep getting error messages around these two lines.
SELECT @ExecutionStatus=current_e xecution_s tatus, @LastRunOutcome=last_run_o utcome
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
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
I keep getting error messages around these two lines.
SELECT @ExecutionStatus=current_e
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
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/
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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'
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'