SQL Server Agent Job Timeout

Question

I have just had a scheduled SQL Server job run for longer than normal, and I could really have done with having set a timeout to stop it after a certain length of time.

I might be being a bit blind on this, but I can't seem to find a way of setting a timeout for a job. Does anyone know the way to do it?

Thanks

Answer 1

What kind of a job is this? You may want to consider putting the whole job in a TSQL script within a While loop. The condition to check would obviously be the time difference between current time and job start time.

Raj

Answer 2

We do something like the code below as part of a nightly job processing subsystem - it is more complicated than this actually in reality; for example we are processing multiple interdependent sets of jobs, and read in job names and timeout values from configuration 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