Finding unclosed/leaked SqlConnections in an ASP.NET environment

• Download source - 0.9 KB

Introduction

If you ever encountered the following exception, here is one approach to find out which connections you forgot to close.

Timeout expired. The timeout period elapsed prior to obtaining a connection
from the pool. This may have occurred because all pooled connections 
were in use and max pool size was reached.

Using the code

To get the necessary information, you will have to keep track of every connection you are creating. To do this, I have implemented the following classes:

Public Class ManagedConnection
  Public CreatingMethod As String
  Private _realConnection As SqlConnection

  Public Property RealConnection() As SqlConnection
    Get
      Return _realConnection
    End Get
    Set(ByVal Value As SqlConnection)
      _realConnection = Value
    End Set
  End Property

  Public Sub New(ByVal pConnectionString As String)
    Me.CreatingMethod = Environment.StackTrace
    _realConnection = New SqlConnection(pConnectionString)
  End Sub
End Class

ManagedConnection stores the StackTrace of every location where you are creating a new connection.

The second class keeps track of all created ManagedConnections:

Public Class ManagedConnectionPool
  Dim _pool As New ArrayList

  Public Function CreateConnection(ByVal pConnectionString) As SqlConnection
    Dim curConn As New ManagedConnection(pConnectionString)
    _pool.Add(curConn)
    Return curConn.RealConnection
  End Function

  Public Sub CheckAndCleanPool()
    Dim curConn As ManagedConnection
    Dim problem As Boolean = False
    Dim message As String
    For Each curConn In _pool
      If curConn.RealConnection.State <> ConnectionState.Closed Then
        curConn.RealConnection.Close()
        message &= curConn.CreatingMethod & Environment.NewLine & Environment.NewLine
        problem = True
      End If
    Next
    _pool.Clear()
    If problem Then
      Dim errorMsg As String = "<pre style='" & _
       "margin-top: 8pt; margin-bottom: 8pt;background-color: #ffffee;" & _
       "white-space:pre;border-style:solid;border-width:1px;border-color:#999999;" & _
       "color:#333333;padding:10px;width:100%;'>Unclosed connection" & _
       Environment.NewLine & Environment.NewLine & message & "</div>"
      HttpContext.Current.Response.Write(errorMsg)
    End If
  End Sub
End Class

If you're using the ManagedConnectionPool every time you need a connection, the class will store all the created connections. Whenever you are finished and think you didn't leave any connections open, you can use the CheckAndCleanPool method to test whether there are still any connections left open.

To automate the whole thing, I have created a base class that you can use for all your web pages. This base class automatically creates a ManagedConnectionPool whose connections are tested as soon as the page is no longer needed.

Public Class BasePage
  Inherits System.Web.UI.Page

  Protected _managedConnectionPool As New ManagedConnectionPool

  Public Overrides Sub Dispose()
    _managedConnectionPool.CheckAndCleanPool()
    MyBase.Dispose()
  End Sub
End Class

To use the whole thing, here is what you have got to do:

• Include the ManagedConnectionPool.vb file in your ASP.NET web project.
• Make sure that your web pages inherit from BasePage rather than from System.Web.UI.Page.
• Whenever you need a connection, use the _managedConnectionPool.CreateConnection( xxx) method instead of calling New SqlConnection.

If you actually leave a connection open, the ManagedConnectionPool will add an error message at the end of the original page output. This message will consist of the StackTrace that leads you to the point where you created the unclosed connection.

History

• March 12, 2006 - Initial release.