遇到 SQL Server“超时"的经典 ASP 应用程序和“SQL Server 不存在或访问被拒绝"
我们已经看到这个问题有一段时间了，我真的很想弄清楚是什么原因造成的.
We've been seeing this problem for a while now and I'm really trying to wrap my head around what's causing it.
每天有几次我们会看到网页开始抛出[Microsoft][ODBC SQL Server Driver]Timeout expired"然后不久页面开始抛出[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server 不存在或访问被拒绝."
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."
我们有许多不同的应用程序连接到这个数据库服务器.它平均每秒处理大约 2500 个并发连接，平均 10,000 个事务.我们的大多数应用程序都没有任何问题，这些问题似乎只发生在 Web 服务器上.(可能和连接池有关?)
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?)
我不确定将此问题归咎于什么.有问题的 SQL 服务器因其所做的工作而被大大压倒，并配备了每处理器许可.所以我不认为我们正在考虑许可/性能问题.
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.
我认为可能存在 IP 连接问题，因此我将 ConnectionString 更改为使用 IP 地址并运行了一些长时间运行的 ping.我在 Web 服务器和数据库服务器之间丢失了 0 个数据包.
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.
ASP 连接字符串现在如下所示:
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;
用户是使用 Sql Server 身份验证连接的非域用户.所以我不认为这是一个与域相关的问题.我检查了 SQL 服务器日志文件，没有发现任何与事件对应的内容.
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.
我发现 另一个 stackoverflow 问题 描述了类似的行为，但没有一个决议.
I've found another stackoverflow question describing similar behavior, but without a resolution.
详情:
- Web 服务器:Windows 2003 Standard SP2、IIS 6.
- 数据库服务器: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?
谢谢！
-佐拉克
编辑
谁能告诉我在经典的高负载 asp 中执行 sql 查询的最佳实践是什么?我们想尝试利用连接池吗?
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
解决方案(根据 ScottE 的建议)
这篇文章描述，到发球台，我的问题.我进行了注册表更改，然后重新启动了服务器.
This article described, to a tee, my problem. I made the registry change and then rebooted the server.
问题解决了！
推荐答案
您的 Web 应用程序是否正在关闭和处理(设置为空)数据库连接?
Is your web app closing and disposing (set to nothing) of database connections?
另外，您是否尝试过使用 SQLOLEDB 代替 ODBC?想不出为什么要在这里使用 ODBC.
Also, have you tried using SQLOLEDB instead of ODBC? Can't think of any reason why you'd be using ODBC here.
这是我在一个非常繁忙的经典 asp 应用程序上的连接字符串:
here's my connection string on a very busy classic asp app:
Dim strcConn
strConn = "Provider=SQLOLEDB; Data Source=someserver; Initial Catalog=somedb; User ID=someuserid; Password=somepassword"
编辑
我看到了这篇博文.有点意思.
I came across this blog posting. Kind of interesting.
http://www.ryanbutcher.com/2006/02/classic-asp-on-2003-server-with.html
相关文章