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.
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)