CODEWITHSUNDEEP
sql-servert-sqltimeout
Michel de Ruiter
Michel de Ruiter

Reputation: 7954

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.

Upvotes: 2

Views: 8690

Answers (3)

Michel de Ruiter
Michel de Ruiter

Reputation: 7954

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.

Upvotes: 1

kubasnack
kubasnack

Reputation: 91

Your DBA can set the timeout to a linked server as well as to queries but a direct query does not let you do so yourself. The bigger question I would have is why does the query fail? Is there a preset timeout already on the server (hopefully), are you running out of memory and paging, or any of a million other reasons. Do you have a DBA? Because if one of my servers was being hammered by such bad code, I would be contacting the person who was executing it. If he hasn't, you should reach out and ask for help determining the failure reason.

Upvotes: 1

RHaberle
RHaberle

Reputation: 11

As far as I know you cannot limit query time unless specified in the connection string (which you can't do) or if the query is executed over a linked server.

Upvotes: 1

Related Questions