SQL Server timeout on DB and into connection string

All we need is an easy explanation of the problem, so here it is.

Recently i have discovered (i have to premit i am not a DBA) that there is the possibility to configure a timeout into SQL Serve property (which apply to all DB into the SQL Server instance) :

SQL Server timeout on DB and into connection string

Ignoring the fact that the picture display a 0 timeout (i am experimenting, it will not be the final configuration); i am confused, because i know that the timeout should be configured into the client that connect to SQL Server instance (both connection and command timeout).

Now i wonder what happening if a SQL Sever configuration say that the timeout is 10 minutes and a client connect with a connection string of 20 minutes (both command and connection).
Which timeout is applied?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

This is not for regular connections, it is outgoing queries to linked servers. I.e., when your SQL Server is acting as a client towards other engines.

The sp_configure setting is named "remote query timeout (s)". Here is a quote from the documentation: "This value applies to an outgoing connection initiated by the Database Engine as a remote query.".

There’s no server-side setting to force a timeout after a certain elapse time (AFAIK). The "closest" you can come, IMO, is the ‘query governor cost limit’ setting. But it isn’t based on elapse time, it is based on estimated query bucks. Also, it doesn’t time-out – it will prohibit start execution based on the estimate.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply