Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Classic ASP application experiencing SQL Server "Timeouts" and "SQL Server does not exist or access denied"

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!

like image 749
zorlack Avatar asked Jun 22 '10 17:06

zorlack


People also ask

How do I fix SQL Server connection timeout?

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.

What causes SQL Server timeout?

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.

How do I change timeout in SQL Server?

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.


1 Answers

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

like image 188
ScottE Avatar answered Sep 27 '22 18:09

ScottE