[ACCEPTED]-SQL Server Agent Job Timeout-sql-server-agent
We do something like the code below as part 6 of a nightly job processing subsystem - it 5 is more complicated than this actually in 4 reality; for example we are processing multiple 3 interdependent sets of jobs, and read in 2 job names and timeout values from configuration 1 tables - but this captures the idea:
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
What kind of a job is this? You may want 4 to consider putting the whole job in a TSQL 3 script within a While loop. The condition 2 to check would obviously be the time difference 1 between current time and job start time.
More Related questions