遇到 SQL Server“超时”的经典 ASP 应用程序和“SQL Server 不存在或访问被拒绝”
我们已经看到这个问题有一段时间了，我真的很想弄清楚是什么原因造成的。
一天中有几次，我们会看到网页开始抛出“[Microsoft][ODBC SQL Server Driver]超时已过期”，然后不久之后页面开始抛出“[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server不存在或访问被拒绝。”
我们有许多不同的应用程序连接到此数据库服务器。它平均每秒有大约 2500 个并发连接处理 10,000 个事务。我们的大多数应用程序都没有任何问题，问题似乎只发生在 Web 服务器上。 （也许它与连接池有关？）
我不确定该问题归因于什么。所讨论的 SQL 服务器对于它所做的工作来说是非常强大的，并且配备了每处理器许可。所以我不认为我们正在考虑许可/性能问题。
我认为可能存在 IP 连接问题，因此我更改了 ConnectionString 以使用 IP 地址并运行了一些长时间运行的 ping。我在 Web 服务器和数据库服务器之间丢失了 0 个数据包。
ASP 连接字符串现在如下所示：
Provider=MSDASQL; Driver={SQL Server}; Server=10.0.100.100; Database=DBName; UID=WebUserName; PWD=WebUserPassword; ConnectionTimeout=15; CommandTimeout=120;
该用户是使用 Sql Server 身份验证进行连接的非域用户。所以我不认为这是一个与域相关的问题。我检查了 SQL Server 日志文件，没有发现任何与该事件相对应的内容。
我发现另一个 stackoverflow 问题描述了类似的行为，但没有解决方案。
详细信息：
- Web 服务器：Windows 2003 Standard SP2、IIS 6。
- 数据库服务器：Microsoft SQL Server 9.0.4035
有人见过/解决了这种类型的问题吗？有人对我接下来应该去哪里有什么建议吗？
谢谢！
-Zorlack
编辑
谁能告诉我在经典高负载 asp 中执行 sql 查询的最佳实践是什么？我们想尝试利用连接池吗？
查看代码，很多看起来像这样：
Set objCn = Server.CreateObject("ADODB.Connection")
objCn.Open(Application("RoConnStr"))
'do some stuff
objCn.Close
Set objCn = Nothing
解决方案（根据 ScottE 的建议）
这篇文章 准确地描述了我的问题。我更改了注册表，然后重新启动了服务器。
问题解决了！
We've been seeing this problem for a while now and I'm really trying to wrap my head around what's causing it.
A couple of times a day we'll see periods where web pages start throwing "[Microsoft][ODBC SQL Server Driver]Timeout expired" then shortly afterward pages start throwing "[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied."
We have many different applications that connect to this database server. It averages around 2500 concurrent connections processing on average 10,000 transactions per second. Most of our applications have no problems whatsoever, the problems seem only to happen on the web server. (Perhaps it's related to connection pooling?)
I'm not sure what to attribute this problem to. The SQL server in question is vastly overpowered for the work that it does, and is equipped with per-processor licensing. So I don't think we're looking at a licensing/performance issue.
I thought maybe there was an IP connectivity issue, so I changed the ConnectionString to use the IP address and ran some long-running pings. I got 0 packets lost between the Web server and the Database server.
The ASP connection string now looks like this:
Provider=MSDASQL; Driver={SQL Server}; Server=10.0.100.100; Database=DBName; UID=WebUserName; PWD=WebUserPassword; ConnectionTimeout=15; CommandTimeout=120;
The user is a non-domain user connecting using Sql Server authentication. So I don't think it's a domain-related issue. I've checked the SQL server log files and have found nothing whatsoever corresponding to the incidents.
I've found another stackoverflow question describing similar behavior, but without a resolution.
The Details:
- Web Server: Windows 2003 Standard SP2, IIS 6.
- Database Server: Microsoft SQL Server 9.0.4035
Has anyone seen/resolved this type issue? Does anyone have any suggestions as to where I should look next?
Thanks!
-Zorlack
EDIT
Can anyone tell me what the best practice is for performing sql queries in classic high-load asp? Do we want to try to leverage connection pooling?
In looking at the code, quite a lot looks like this:
Set objCn = Server.CreateObject("ADODB.Connection")
objCn.Open(Application("RoConnStr"))
'do some stuff
objCn.Close
Set objCn = Nothing
Solution (per ScottE's advice)
This article described, to a tee, my problem. I made the registry change and then rebooted the server.
Problem Solved!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您的网络应用程序是否关闭并处置（设置为空）数据库连接？
另外，您是否尝试过使用 SQLOLEDB 而不是 ODBC？想不出您在这里使用 ODBC 的任何原因。
这是我在一个非常繁忙的经典 asp 应用程序上的连接字符串：
编辑
我发现了这篇博客文章。有点有趣。
http://www.ryanbutcher.com/2006 /02/classic-asp-on-2003-server-with.html
Is your web app closing and disposing (set to nothing) of database connections?
Also, have you tried using SQLOLEDB instead of ODBC? Can't think of any reason why you'd be using ODBC here.
here's my connection string on a very busy classic asp app:
Edit
I came across this blog posting. Kind of interesting.
http://www.ryanbutcher.com/2006/02/classic-asp-on-2003-server-with.html
我通过重新创建存储过程解决了这个问题！
在我的例子中，只需简单的
DROP，然后CREATE就停止了超时！我已经被这个问题困扰了一个星期了；当我可以在不到一秒的时间内直接在数据库上运行相同的查询时，经典的 ASP 会说“SQL 超时”。 （虽然我没有看到“不存在”消息。）ASP 整整一个月都工作正常。
我的一位天才朋友说：“通过 ASP 使用‘缓存’执行计划，这不是很高效。尝试删除并重新创建。这确实表明您的存储过程可以重新编写，以使其更加高效，因为它可能会再次发生。”
由于在使用 SQL Management Studio 进行测试时该过程运行良好，因此我假设它没有使用缓存计划，但 ASP 却使用了。
I solved this problem by recreating the stored procedure!
Just a simple
DROPand thenCREATEstopped the timeouts in my case!I had suffered with this problem for a week; a classic ASP said "SQL timeout" when I could run the same query direct on the database in less than a second. (I did not see the "does not exist" message though.) The ASP had been working fine for a whole month.
A genius friend of mine said, "Going through ASP uses a 'cached' execution plan, which is not very efficient. Try dropping and recreating. This does suggest your stored procedure could do with re-writing to make it more efficient, as it could happen again."
Since the proc was running fine when tested with SQL Management Studio, I assume that does not used the cached plan, but ASP does.
当我遇到此类问题时，总是不得不让某人在客户端应用程序中打开数据，而不提交“select ..”语句。
不知道这是否能解决您的问题...不过。
The times I had such problems, always had to to with someone holding the data open in a client application without commiting a 'select ..' statement.
Don't know if this solves your problem here... though.