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?
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
KILLthe 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.