sql-serverssissql-agent-job

How to terminate the SQL server agent job after X minutes?


I have a job running on MS SQL Server under SQL Agent. The job basically runs a dtsx package and it is running every 5 minutes. Is there way that the job automatically terminates if it is running more than 4 minutes? Normally the job completes within 3 seconds.


Solution

  • Following approach might to solve this issue (we got it a bit differently - we have a stored procedure which evaluates, if the job is running beforehand and then performs the exec): you can get the runtime of the required job via a SQL query:

    WITH cte AS(
      SELECT sj.name, sja.start_execution_date
         ,ROW_NUMBER() OVER (ORDER BY sja.start_execution_date DESC) AS rn
      FROM msdb.dbo.sysjobactivity AS sja
      INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
      WHERE sja.start_execution_date IS NOT NULL
         AND sja.stop_execution_date IS NULL
         AND sj.name LIKE '%myJobName%'
    )
    SELECT DATEDIFF(SECOND, start_execution_date, GETDATE())/60. AS RunTimeMinutes
      FROM cte
      WHERE rn = 1
    

    This you can load into a variable. In the next step you can do something like:

    IF @MyVar >= 4 THEN EXEC '[msdb].[dbo].[sp_stop_job] N' + char(39) + 'myJobName' + char(39)