ASP.NET Web site spawing hundereds of Connections to SQL Server Express instance - how can I identify culprit code?

Question

This is a quite a big, quite a badly coded ASP.NET website that I am currently tasked with maintaining. One issue that I'm not sure how to solve is that at seemingly random times, the live web site will lock up completely, page access is ok, but anything that touches the database causes the application to hang indefinitely.

The cause seems to be many more open connections to the database than you would expect of a lowish level traffic web site. Activity monitor shows 150+ open connections, most with an Application value of '.NET SqlClient Data Provider', with the network service login.

A quick solution is to restart the SQL Server service (I've recycled the ASP.NET app pool also just to ensure that the application lets go of anything, and stops any code from reattempting to open connections if there was some sort of loop process that I'm unaware of). This doesn't however help me solve the problem.

The application uses a Microsoft SQLHelper class, which is a pretty common library, so I'm fairly confident that the code that uses this class will have connections closed where required.

I have however spotted a few DataReaders that are not closed properly. I think I'm right in saying that a DataReader can keep the underlying connection open even if that connection is closed because it is a connected class (Correct me if I'm wrong).

Something that it perculiar is that one of the admins restarted the server (not the database server, the actual server) and immediatley, the site would hang again. The culprit was again 150+ open database connections.

Does anybody have any diagnostic technique that they can share with me for working out where this is happening?

Update: SQL Server Log files show many entries like this (30+)

2010-10-15 13:28:53.15 spid54 Starting up database 'test_db'.

I'm wondering if the server is getting hit by an attacker. That would explain the many connections right after boot, and at seemingly random times.

Update: Have changed the AutoClose property, though still hunting for a solution!

Answer 1

Update:

Lots and lots of Starting up database: Set the AutoClose property to false : REF

---

You are correct about your DataReaders: make sure you close them. However, I have experienced many problems with connections spawning out of control even when connections were closed properly. Connection pooling didn't seem to be working as expected since each post-back created a new SqlConnection. To avoid this seemingly uneeded re-creation of the connection, adopted a Singleton approach to my DAL. So I create a single DataAdapter and send all my data requests through it. Although I've been told that this is unwise, I have not received any support for that claim (and am still eager to read any documentation/opinion to this effect: I want to get better, not be status quo). I have a DataAdapter class for you to consider if you like.

If you are in SQL 2005+, you should be able to use Activity Monitor to see the "Details" of each connection which sometimes gives you the last statement executed. Perhaps this will help you track the statement back to some place in code.

Answer 2

I would recommend downloading http://sites.google.com/site/sqlprofiler/ to see what queries are happening, and sort of work backwards from there. Good luck man!

Answer 3

Many types such as DbConnection, DbCommand and DbDataReader and their derived types (SqlConnection, SqlCommand and SqlReader) implement the IDisposable interface/pattern.

Without re-gurgitating what has already been written on the subject you can take advantage of this mechanism via the using statement.

As a rule of thumb you should always try to wrap your DbConnection, DbCommand and DbDataReader objects with the using statement which will generate MSIL to call IDisposable's Dispose method. Usually in the Dispose method there is code to clean up unmanaged resources such as closing database connections.

For example:

using(SqlConnection cn = new SqlConnection(connString))
{
  using(SqlCommand cmd = new SqlCommand("SELECT * FROM MyTable", cn))
  {
    cmd.CommandType = CommandType.Text;
    cn.Open();

    using(SqlDataReader rdr = cmd.ExecuteReader())
    {
      while(rdr.Read())
      { 
        ... do stuff etc 
      }
    }
  }
}

This will ensure that the Dispose methods are called on all of the objects used immediately after use. For example the Dispose methods of the SqlConnection closes the underlying database connection right away instead of leaving it around for the next garbage collection run.

Little changes like this improve your applications ability to scale under heavy load. As you already know, "Acquire expensive resources late and release early". The using statement is a nice bit of syntactic sugar to help you out.

If you're using VB.NET 2.0 or later it has the same construct:

Using Statement (Visual Basic) (MSDN)
using Statement (C# Reference) (MSDN)