Is it possible to set a timeout for a SQL query on Microsoft SQL Server?

Question

I've got a scenario when sometimes a user selects the right parameters and makes a query which takes several minutes or more to execute. I cannot prevent him to select such a combination of parameters (it's quite legal), so I'd like to set a timeout on the query.

Note that I really want to stop the query execution itself and rollback any transactions, because otherwise it hogs up most of server resources. Add an impatient user who restarts the application and tries the combination again, and you've got a recipe for a disaster (read: SQL Server DoS).

Can this be done and how?

Answer 1

As far as I know, apart from setting the command or connection timeouts in the client, there is no way to change timeouts on a query by query basis in the server.

You can indeed change the default 600 seconds using sp_configure, but these are server scoped.

Answer 2

Humm! did you try LOCK_TIMEOUT
Note down what it was orginally before running the query
set it for your query
after running your query set it back to original value

SET LOCK_TIMEOUT 1800;  
SELECT @@LOCK_TIMEOUT AS [Lock Timeout];  

Answer 3

I might suggest 2 things.

1) If your query takes a lot of time because it´s using several tables that might involve locks, a quite fast solution is to run your queries with the "NoLock" hint.

Simply add Select * from YourTable WITH (NOLOCK) in all your table references an that will prevent your query to block for concurrent transactions.

2) if you want to be sure that all of your queries runs in (let´s say) less than 5 seconds, then you could add what @talha proposed, that worked sweet for me

Just add at the top of your execution

SET LOCK_TIMEOUT 5000;   --5 seconds.

And that will cause that your query takes less than 5 or fail. Then you should catch the exception and rollback if needed.

Hope it helps.