T-sql: Stop Query After Certain Time

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 co

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

Solution 2:

What will you be using to execute this query? If you create a .NET application, the timeout for stored procedures by default is 30 seconds. You can change the timeout to be 6 minutes if you wish by changing SqlCommand.CommandTimeout

Solution 3:

Yes, let's try it out.

This is a query that will run for 6 minutes:

DECLARE@iINT=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 queryDECLARE@Search_for_query NVARCHAR(300) SET@Search_for_query ='%FORMAT(GETDATE(),''hh:mm:ss'')%'-- Define the maximum time you want the query to runDECLARE@Time_to_run_in_minutes INTSET@Time_to_run_in_minutes =1DECLARE@SPID_older_than smallintSET@SPID_older_than = (
                                    SELECT--text,
                                    session_id
                                    --,start_timeFROM sys.dm_exec_requests  
                                    CROSS APPLY sys.dm_exec_sql_text(sql_handle)  
                                    WHERE text LIKE@Search_for_query       
                                    AND text NOTLIKE'%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 testingDECLARE@SQL nvarchar(1000)
SET@SQL='KILL '+CAST(@SPID_older_than asvarchar(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

Solution 4:

In SQL Server, I just right click on the connection in the left Object Explorer pane, choose Activity Monitor, then Processes, right click the query that's running, and choose Kill Process.