Sql connection waits 15 seconds despite 3 seconds timeout in connection string

Question

I have a website using Microsoft SQL 2008 server over local network. Sometimes, SQL server machine is rebooted, and so the website fails to connect to the database. If the machine is up and running, it will respond fast. If it's down, there is no need to wait for 15 seconds. 3 seconds are ok.

I want to display apologizes on the website when the database is not reachable, and want to do it fast. But setting Connection Timeout=3 in connection string seems having no effect. The page spends 22 seconds to wait before throwing SqlException on SqlConnection.Open();.

What's wrong with it? May it be a hidden configuration which overrides the timeout?

Currently, my connection string is

Data Source=...;
Initial Catalog=...;
Integrated Security=True;
Connection Timeout=3

If I set it to ...;ConnectionTimeout=3 (without space),

System.ArgumentException: Keyword not supported: 'connectiontimeout'.

is thrown (strange, MSDN documentation indicates that we can use both strings).

Involute

Note: checking the value of SqlConnection.ConnectionTimeout gives '3', as expected. So the connection string seems to be correct. – Involute

try telnet to check if the timeout is specific to the network card – Obtuse

@Sheng Jiang 蒋晟: what must I do exactly? I tried to connect, it seems like it spent 20 s. before a "Network error: Connection timed out", so the same time that the web page spend trying to connect to SQL server. What does it mean? How can it help? – Involute

any difference if you remove the timeout from the connection string and just set the property to 3 before calling Open()? Doesn't sound like there would be, but wanted to check. Also, any difference if you SqlConnection.ClearAllPools before creating the new sqlconnection? wondering if it's using a pooled connection so the connect timeout isn't used because it's already connected. – Undue

It probably mean there is a timeout before the network driver reports destination unreachable set in the firmware. – Obtuse

@James: timeout property is read-only, so the only way to set it is through the connection string. Tried SqlConnection.ClearAllPools(). The result is the same. – Involute

@Sheng: that's it, I think. So the workaround would be to call SqlConnection.Open asynchronously, and set EndInvoke it after setting timeout with WaitOne. Can you please promote your last comment to an answer so I would accept it? – Involute

Answer 1

There is a timeout before the networking hardware reports connection timeout to the network drivers, which in turn notifies the programs waiting for network IO. You can verify transport layer timeouts via telnet servername 1433 (assuming your sql server is listening on port 1433).

But 3 seconds is way too short for a process to initialize the network APIs (assuming your web app is in its own application pool), send request and wait for the hardware to timeout. Updating BIOS/firmware/driver probably won't reduce the response time that much.

It would be better to carry out the connection asynchronously. i do not suggest using EndInvoke to end the asynchronous call as unlucky users may still need to wait 3 full seconds to see any response when the database is down. Maybe an Ajax call is better. If you have a lot of users constantly hitting your web site, you may want to cache the result of connectivity checking and update it in a manner meaningful to your users.

Hammett

Answer 2

The following blog post assisted me in solving this problem: http://improve.dk/controlling-sqlconnection-timeouts/

Iceberg

Answer 3

ConnectionTimeout without space is the property name when accessing via code, not for the connection string.

Not sure if this is of use, but when I hit this issue in the past, it was because I also needed to set SqlCommand.CommandTimeout. What happened for me is the connection was opened successfully, then DB server went down, then my next command did not timeout as quickly as I expected based on the Connection Timeout, and this was due to the CommandTimeout needing to be set as well.

Lorenalorene

CommandTimeout is a property of SqlCommand, so it will not affect SqlConnection.Open() and cannot be used at this level. Also, changing command timeout on the server (600 s. by default) will not help, since the SQL server is down. – Involute