尽管连接字符串超时 3 秒，Sql 连接仍等待 15 秒
我有一个通过本地网络使用 Microsoft SQL 2008 服务器的网站。有时，SQL Server 机器会重新启动，因此网站无法连接到数据库。如果机器启动并运行，它将快速响应。如果已关闭，则无需等待 15 秒。 3秒就可以了。
我想当数据库无法访问时在网站上显示抱歉，并且希望快速完成。但是在连接字符串中设置 Connection Timeout=3 似乎没有效果。页面在 SqlConnection.Open(); 上抛出 SqlException 之前需要等待 22 秒。
有什么问题吗？它可能是一个覆盖超时的隐藏配置吗？
目前，我的连接字符串是
Data Source=...;
Initial Catalog=...;
Integrated Security=True;
Connection Timeout=3
如果我将其设置为 ...;ConnectionTimeout=3 （没有空格），
System.ArgumentException: Keyword not supported: 'connectiontimeout'.
则会抛出（奇怪，MSDN 文档 表明我们可以使用这两个字符串）。
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).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
在网络硬件向网络驱动程序报告连接超时之前有一个超时，网络驱动程序又通知等待网络 IO 的程序。您可以通过 telnet servername 1433 验证传输层超时（假设您的 sql 服务器正在侦听端口 1433）。
但对于初始化网络 API（假设您的 Web 应用程序位于其自己的应用程序池中）、发送请求并等待硬件超时的进程来说，3 秒太短了。更新 BIOS/固件/驱动程序可能不会减少太多响应时间。
最好是异步进行连接。我不建议使用 EndInvoke 来结束异步调用，因为不幸的用户可能仍然需要等待 3 秒才能在数据库关闭时看到任何响应。也许 Ajax 调用更好。如果您有很多用户不断访问您的网站，您可能需要缓存连接检查的结果并以对您的用户有意义的方式更新它。
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.
以下博客文章帮助我解决了这个问题：
http://improve.dk/controlling-sqlconnection-timeouts/
The following blog post assisted me in solving this problem:
http://improve.dk/controlling-sqlconnection-timeouts/
不带空格的
ConnectionTimeout是通过代码访问时的属性名称，而不是连接字符串。不确定这是否有用，但是当我过去遇到这个问题时，是因为我还需要设置
SqlCommand.CommandTimeout。对我来说发生的事情是连接成功打开，然后数据库服务器关闭，然后我的下一个命令没有像连接超时所预期的那样快超时，这是由于CommandTimeout需要也要设置。ConnectionTimeoutwithout 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 theCommandTimeoutneeding to be set as well.