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
Set(ByVal Value As SqlConnection)
_realConnection = Value
Public Sub New(ByVal pConnectionString As String)
Me.CreatingMethod = Environment.StackTrace
_realConnection = New SqlConnection(pConnectionString)
ManagedConnection stores the
StackTrace of every location where you are creating a new connection.
The second class keeps track of all created
Public Class ManagedConnectionPool
Dim _pool As New ArrayList
Public Function CreateConnection(ByVal pConnectionString) As SqlConnection
Dim curConn As New ManagedConnection(pConnectionString)
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
message &= curConn.CreatingMethod & Environment.NewLine & Environment.NewLine
problem = True
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>"
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
Protected _managedConnectionPool As New ManagedConnectionPool
Public Overrides Sub Dispose()
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
- Whenever you need a connection, use the
_managedConnectionPool.CreateConnection( xxx) method instead of calling
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.
- March 12, 2006 - Initial release.