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:
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!
If you encounter a connection-timeout error, follow the steps: Increase the connection-timeout parameter. If you use an application to connect to SQL Server, increase the relevant connection-timeout parameter values and check whether the connection eventually succeeds.
These values are set by the client connecting to the SQL Server. An operation timeout occurs when a command takes too long to complete, and the client raises an error. Getting this timeout error indicates queries are taking longer than expected.
Using SQL Server Management StudioIn Object Explorer, right-click a server and select Properties. Click the Connections node. Under Remote server connections, in the Remote query timeout box, type or select a value from 0 through 2,147,483,647 to set the maximum number seconds for SQL Server to wait before timing out.
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:
Dim strcConn
strConn = "Provider=SQLOLEDB; Data Source=someserver; Initial Catalog=somedb; User ID=someuserid; Password=somepassword"
Edit
I came across this blog posting. Kind of interesting.
http://www.ryanbutcher.com/2006/02/classic-asp-on-2003-server-with.html
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With