title: SQL Agent: Set a Max Execution Time
body: <p>Afternoon.  I have several SQL Agent jobs running on an MS 2K8 BI server, some of them on a daily basis, others hourly, and one every two minutes (a heartbeat monitor for another process).  There is also an app which imports data every few minutes, around the clock.  Occasionally some combination of updates and reports collide and one or another hangs for a half hour or more, instead of the usual 60 seconds.</p>
  
  <p>While I need to get to the root of these race conditions, in the meantime I'd like to set certain jobs to automatically die after, say, five minutes.  I can do this in SSIS or a Windows scheduled task, but I don't see any way to do so in SQL Agent.  Is this possible, or do I need to wrap the task in an SSIS package to get this kind of control?</p>
  
  <hr>
  
  <p>FYI, here's the SQL Agent job I ended up using:</p>
  
  <pre><code>DECLARE @Cancelled BIT
  EXEC dbo.CancelJob @JobName = 'ETL - Daily', @Cancelled = @Cancelled OUT
  
  IF @Cancelled = 1
      BEGIN
      DECLARE @Success INT
      EXEC @Success = msdb..sp_send_dbmail
          @profile_name = 'Reporting',
          @recipients = 'reporting@mycompany.com',
          @subject = 'Cancelled Daily ETL'
      IF @Success &lt;&gt; 0 RAISERROR('An error occurred while attempting to send an e-mail.', 16, @Success)
      END
  </code></pre>
  
  <p>...and here's the code behind <code>CancelJob</code>:</p>
  
  <pre><code>CREATE PROCEDURE dbo.CancelJob(@JobName VARCHAR(100), @OwnerName VARCHAR(100) = NULL, @Cancelled BIT OUT)
  AS BEGIN
      IF @OwnerName IS NULL SET @OwnerName = SUSER_NAME()
      SET @Cancelled = 0
  
      CREATE TABLE #JobInfo
          ( 
          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,  -- This is the only field we want (sigh)
          Current_Step    INT,
          Current_Retry_Attempt   INT,
          State   INT
          )
      INSERT INTO #JobInfo
          EXEC xp_sqlagent_enum_jobs 1, @OwnerName
  
      DECLARE @Running INT = (SELECT Running FROM #JobInfo AS JI INNER JOIN msdb..sysjobs_view AS J ON JI.Job_ID = J.job_id WHERE J.name = @JobName)
      IF @Running = 1
          BEGIN
          BEGIN TRY
              EXEC msdb..sp_stop_job @job_name = @JobName
              SET @Cancelled = 1
          END TRY
          BEGIN CATCH
              -- If an error occurs, it is *probably* because the job finished before we could cancel it, which is fine
          END CATCH
          END
  END
  GO
  </code></pre>
  
  <p><code>xp_sqlagent_enum_jobs</code> was the trick to avoid the uncatchable error.</p>