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

Question

Hi,

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).

Answer 1

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.

Answer 2

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.