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

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

Solution 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];  

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