Click here to Skip to main content
Click here to Skip to main content

Finding unclosed/leaked SqlConnections in an ASP.NET environment

, 12 Mar 2006
Rate this:
Please Sign up or sign in to vote.
How to find leaked connections.

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

fschudel
Web Developer
Switzerland Switzerland
No Biography provided

Comments and Discussions

 
GeneralAren't you woried Pinmemberlunky15-Mar-06 1:23 
GeneralRe: Aren't you woried Pinmemberfschudel16-Mar-06 4:15 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 12 Mar 2006
Article Copyright 2006 by fschudel
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid