SQL Server 2000 intermittent connection exceptions on production server - specific environment problem?

Question

We've been having intermittent problems causing users to be forcibly logged out of out application.

Our set-up is ASP.Net/C# web application on Windows Server 2003 Standard Edition with SQL Server 2000 on the back end. We've recently performed a major product upgrade on our client's VMWare server (we have a guest instance dedicated to us) and whereas we had none of these issues with the previous release the added complexity that the new upgrade brings to the product has caused a lot of issues. We are also running SQL Server 2000 (build 8.00.2039, or SP4) and the IIS/ASP.NET (.Net v2.0.50727) application on the same box and connecting to each other via a TCP/IP connection.

---

Primarily, the exceptions being thrown are:

System.IndexOutOfRangeException: Cannot find table 0.

System.ArgumentException: Column 'password' does not belong to table Table.

[This exception occurs in the log in script, even though there is clearly a password column available]

System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.

[This one is occurring very regularly]

System.InvalidOperationException: This SqlTransaction has completed; it is no longer usable.

System.ApplicationException: ExecuteReader requires an open and available Connection. The connection's current state is connecting.

System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

And just today, for the first time:

System.Web.UI.ViewStateException: Invalid viewstate.

---

We have load tested the app using the same number of concurrent users as the production server and cannot reproduce these errors. They are very intermittent and occur even when there are only 8/9/10 user connections. My gut is telling me its ASP.NET - SQL Server 2000 connection issues..

We've pretty much ruled out code-level Data Access Layer errors at this stage (we've a development team of 15 experienced developers working on this) so we think its a specific production server environment issue.

Answer 1

I know you don't want to hear this, but people smarter than I have said it (check out McConnell's Code Complete if you don't believe me):

It's probably your code, and your gut is probably correct:

My gut is telling me its ASP.NET - SQL Server 2000 connection issues..

The Errors being thrown are quite specific, and contextually, they look like they're just trying to connect and having a hard time -- which if it only happens in the client's environment, could be indicative of a setting not set correctly for the VM to access TCP connections on the host machine (under a different instance).

---

Are you sure that none of your code has changed since before the move, and that your previous environment had logging like this enabled? It may have been happening (to a lesser degree) before, but your environment didn't catch it because you didn't have logging enabled.

If that's not the issue, and I'm reading your post correctly: You're running a server on a guest instance provided by the client on their pipe and bandwidth? If that's the case, then quite possibly (around the same time as that upgrade) some routing configuration was changed, or firewall changes were made, or whatever box the instance is on had some change made now that it handles your stuff differently.

If you can't reproduce it in your environment, and you are 100% certain that it isn't your code; then logically it can only be their environment that is the issue.

Answer 2

Normally, I would agree that you should suspect your own code (as Gortok suggested); BUT I have seen exactly these symptoms before.

It was narrowed down, but not 100% proven to be due to connections not being explicitly closed in code. The devs went through the code base and explicitly closed connections when they were finished with, and the problem went away. Of course, it could have just been coincidence!

Answer 3

Thanks for having a look at my question lads, I appreciate it.

the code has gone under stress testing, 2 passes of regression testing and our testing environment is as closely matched to the client as possible - except for the VMWare set-up. the client is providing us with two separate boxes (so we can split up the app and SQL server) without VMWare running just to try and rule out the VMWare issue.

@Mitch Wheat:

All connection are being explicitly closed AND are implicitly closed by a 'using' loop. We've even overridden the 'disposing' method with a view to complete transparency at code level - we want to know exactly what's happening at all times.

The upgrade was a pretty big upgrade, enhancing both the complexity and size of the app and DB. So the previous version might not have had this trouble (i'm checking through the logs as we speak) it seems as though this issue has just begun on this new release.

Answer 4

The Invalid Viewstate error is pretty common in a high traffic web site. Though, if you recently moved to multiple web servers, make sure you're sharing the same machine key so Viewstate is signed with the same key on all servers. http://www.codinghorror.com/blog/archives/000132.html

Based on the other errors I'd guess that you are using shared connections across multiple threads. Are your connections stored in static variables, Application state, Session state, or other object that's used across multiple requests? Maybe there's a hashtable somewhere containing connections, commands, or transactions. None of the ADO.Net objects are thread safe. So, make sure you only use them in a single threaded fashion.

Another possibility is you're passing around the ADO.NET objects and not consistently disposing of them and managing their scope. Maybe they're cached in the request context or some such?

Answer 5

Lads, just as an update, it turned out that the problem was VMWare related under heavy usage - what a fun week! We're changing the code around to suit the VMWare environment and we've seen some improvement already.

Thanks for the suggestions, I appreciate it.

Answer 6

Hi, we have similar problems with connection interruptions between our application server and a MSSQL server - both hosted different virtual machines on VMWare. Could you tell us about how to suit the code to the VMWare and some background?