Search code examples
sql-servert-sqltimeout

Set query timeout in T-SQL

I'm executing queries to SQL Server from an application. Sometimes one of those queries lasts very long. Too long actually, it usually indicates that it will eventually fail. I would like to specify a maximum duration, after which the query should just fail.

Is there a way to specify a command timeout in T-SQL?

I know a (connection and) command timeout can be set in the connection string. But in this application I cannot control the connection string. And even if I could it should be longer for the other queries.

Solution

  • If the unexpectedly long duration happens to be due to a (local) lock, there is a way to break out of it. Set the lock timeout before running the query:

    SET LOCK_TIMEOUT 600000 -- Wait 10 minutes max to get the lock.

    Do not forget to set it back afterwards to prevent subsequent queries on the connection from timing out:

    SET LOCK_TIMEOUT -1 -- Wait indefinitely again.