How to kill agent job if it does not finish after x min

I want to create an agent job and collect some data on my server, e.g. every 10 minutes. But I would like to kill the job if it does not finish within a specified time period, e.g. after 5 min. I have searched a bit and there does not seem to be a lot of information on this topic and some say I have to create another job to kill the first job.

What is the best way to implement what I would like to implement?

Thanks

Let’s take as an example this query that will run for 60 seconds:

DECLARE @i INT = 1;
WHILE (@i <= 60)                        
BEGIN
    WAITFOR DELAY '00:00:01'            
    print FORMAT(GETDATE(),'hh:mm:ss')
    SET  @i = @i + 1;
END

We want to stop it if the query runs for more than 10 seconds.

So we create an agent job and as step we set this query:

DECLARE @SPID_older_than_10_seconds smallint
SET @SPID_older_than_10_seconds = (
                                    SELECT 
                                    --text,
                                    session_id
                                    --,start_time
                                    FROM sys.dm_exec_requests  
                                    CROSS APPLY sys.dm_exec_sql_text(sql_handle)  
                                    WHERE text LIKE '%FORMAT(GETDATE(),''hh:mm:ss'')%'          -- Put here a part of the code you are targeting or even the whole query
                                    AND text NOT LIKE '%sys.dm_exec_sql_text(sql_handle)%'      -- This will avoid the killing job to kill itself 
                                    AND start_time < DATEADD(SECOND, -10, GETDATE())            -- Select all queries that are 10 seconds old
                                    )

-- SELECT @SPID_older_than_10_seconds                                                           -- Use this for testing

DECLARE @SQL nvarchar(1000)
SET @SQL = 'KILL ' + CAST(@SPID_older_than_10_seconds as varchar(20))
EXEC (@SQL)

This query basically is doing 3 things:

• Search for a running query with a specific text, in our case is print FORMAT(GETDATE(),'hh:mm:ss'). We are using this text as example but you can later set what you want
• Count the difference between when the query started and the time now and find everything older then 10 seconds after the start time
• KILL the SPID linked to that query

Don’t forget to assign the Agent Job to a valid user, in my case is sa but you can choose whatever you want:

We are going to make it run every 10 seconds because we cannot set it to run every second. It doesn’t matter, it’s just an example, then you will set this up to 1 minutes or 5 minutes in your real life problem:

Done, as you can see the query was terminete after around 10 seconds:

Keep in mind that this is just an example with seconds and you have to adapt it to your real life scenario and to your query.
Sometimes you might experience a delay longer then 10 seconds, this because the job run every 10 seconds and you have to calculate the difference from when you run the query and the job is run.

Frankly I believe such delay will be less important if you deal with minutes instead of seconds.