A classic ASP application that is experiencing SQL Server timeouts and SQL Server does not exist or is not accessible.

Question

We have seen this problem for a while, and I'm really trying to wrap my head around what causes it.

Several times a day, we will see periods when web pages start throwing "[Microsoft] [ODBC SQL Server Driver] Timeout expired", and then soon they start throwing pages "[Microsoft] [ODBC SQL Server Driver] [DBNETLIB] SQL Server is not exists or access denied. "

We have many different applications that connect to this database server. On average, it processes about 2500 concurrent connections with an average of 10,000 transactions per second. Most of our applications have no problems, problems seem to occur only on the web server. (Perhaps this is due to the connection pool?)

I'm not sure what this refers to. The SQL server in question has largely overcome the work that it performs and is licensed on each processor. Therefore, I do not think that we are considering a licensing / performance issue.

I thought that there might be a problem with the IP connection, so I changed ConnectionString to use the IP address and ran some lengthy pings. I lost 0 packets 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 using Sql server authentication. Therefore, I do not think this is a domain related problem. I checked the SQL server log files and did not find anything that matched the incidents.

I found an https://stackoverflow.com/a/166708/2/167708 which describes similar behavior, but without permission.

Details:

• Web Server: Windows 2003 Standard SP2, IIS 6.
• Database Server: Microsoft SQL Server 9.0.4035

Has anyone seen / resolved this issue? Does anyone have any suggestions as to where I should look further?

Thanks!

-Zorlack

EDIT

Can someone tell me what is best suited to execute sql queries in classic asp with high load? Do we want to try using a connection pool?

When looking at the code, pretty much looks like this:

 Set objCn = Server.CreateObject("ADODB.Connection") objCn.Open(Application("RoConnStr")) 'do some stuff objCn.Close Set objCn = Nothing 

Solution (advised by ScottE)

This article described my issue. I made a registry change and then rebooted the server.

The problem is solved!

Answer 1

Is your web application closing and removing (nothing established) database connections?

Also, have you tried using SQLOLEDB instead of ODBC? I can’t think of why you are using ODBC here.

here is my connection string in a very busy classic asp application:

 Dim strcConn strConn = "Provider=SQLOLEDB; Data Source=someserver; Initial Catalog=somedb; User ID=someuserid; Password=somepassword" 

Edit

I stumbled upon this blog post. The view is interesting.

http://www.ryanbutcher.com/2006/02/classic-asp-on-2003-server-with.html

Answer 2

I solved this problem by recreating the stored procedure!
Just a simple DROP and then CREATE stopped the timeouts in my case!

I suffered from this problem for a week; classic ASP said "SQL timeout" when I could run the same query directly in the database in less than a second. (I did not see the message “does not exist.”) ASP worked perfectly for a month.

One genius friend of mine said: “Moving through ASP uses a“ cached ”execution plan, which is not very efficient. Try to opt out and recreate. This suggests that your stored procedure can do re-writing to make it more efficient, because it could happen again. "

Since proc worked fine when testing with SQL Management Studio, I assume it doesn't use a cached plan, but ASP does.

Answer 3

The time when I had such problems, I always had to keep data with someone in the client application without commenting on the "select .." statement.

I don't know if this problem solves here ... though.