T-SQL: Stop query after certain time

Question

I am looking to run a query in t-SQL (MS SQL SMS) that will stop after X number of seconds. Say 30 seconds. My goal is to stop a query after 6 minutes. I know the query is not correct, but wanted to give you an idea.

Select * from DB_Table
where (gatedate()+datepart(seconds,'00:00:30')) < getdate()

Answer 1

In SQL Server Management Studio, bring up the options dialog (Tools..Options). Drill down to "Query Execution/SQL Server/General". You should see something like this:

The Execution time-out setting is what you want. A value of 0 specifies an infinite time-out. A positive value the time-out limit in seconds.

NOTE: this value "is the cumulative time-out for all network reads during command execution or processing of the results. A time-out can still occur after the first row is returned, and does not include user processing time, only network read time." (per MSDN).

If you are using ADO.Net (System.Data.SqlClient), the SqlCommand object's CommandTimeout property is what you want. The connect string timeout verb: Connect Timeout, Connection Timeout or Timeout specifies how long to wait whilst establishing a connection with SQL Server. It's got nothing to do with query execution.

Answer 2

Yes, let's try it out.

This is a query that will run for 6 minutes:

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

Now create an Agent Job that will run every 10 seconds with this step:

-- Put here a part of the code you are targeting or even the whole query
DECLARE @Search_for_query NVARCHAR(300) SET @Search_for_query = '%FORMAT(GETDATE(),''hh:mm:ss'')%'
-- Define the maximum time you want the query to run
DECLARE @Time_to_run_in_minutes INT SET @Time_to_run_in_minutes = 1

DECLARE @SPID_older_than smallint
SET @SPID_older_than = (
                                    SELECT 
                                    --text,
                                    session_id
                                    --,start_time
                                    FROM sys.dm_exec_requests  
                                    CROSS APPLY sys.dm_exec_sql_text(sql_handle)  
                                    WHERE text LIKE @Search_for_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(MINUTE, -@Time_to_run_in_minutes, GETDATE())            
                                    )

-- SELECT @SPID_older_than                                                           -- Use this for testing

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

Make sure the job is run by sa or some valid alternative.

Now you can adapt it to your code by changing:

• @Search_for_query = put here a part of the query you are looking for
• @Time_to_run_in_minutes = the max number of minutes you want the job to run