Set query timeout in T-SQL

Question

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.

This is a pretty good thread on it. I love sp_WhoIsActive ans some of the sys functions to see long queries. Granted you'll have to run a query ever so often to see if your threshold is met and then kill it. dba.stackexchange.com/questions/66249/… — S3S
– S3S, Commented Feb 10, 2017 at 16:15
You can't set it in the connection string either. That is just the connect timeout. — Martin Smith
– Martin Smith, Commented Feb 12, 2017 at 0:22

RHaberle · Accepted Answer · 2017-02-12 00:12:22Z

1

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.

answered Feb 12, 2017 at 0:12

RHaberle

111 bronze badge

Sign up to request clarification or add additional context in comments.

2 Comments

Michel de Ruiter Over a year ago

I was afraid so. What if it's over a linked server?

RHaberle Over a year ago

If it's over a linked server you can set the timeout limit of the target server for remote queries, this would apply to all queries. This is an inadvisable solution for many reasons, one of which is the ability to generate a proper execution plan.

kubasnack · Accepted Answer · 2017-02-12 20:27:26Z

1

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.

answered Feb 12, 2017 at 20:27

kubasnack

914 bronze badges

1 Comment

Michel de Ruiter Over a year ago

The execution of the queries is deliberate and I am not sure why you think any code would be bad. Problem is that the delay only happens very rarely (and at night) so we have no idea where to look. I'm just looking for a way to break out.

Michel de Ruiter · Accepted Answer · 2018-04-24 07:28:45Z

1

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.

answered Apr 24, 2018 at 7:28

Michel de Ruiter

8,1457 gold badges55 silver badges81 bronze badges

Collectives™ on Stack Overflow

Set query timeout in T-SQL

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related