How do I solve a connection pool issue?

I have recently started encountering Database connection issues with SQL Server on my development machine.

System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool

How can I monitor the connection pool to figure out what is happening?

Further Info:

I haven't had much luck with this - I'm definitely not leaking connections. Every connection is inside a using statem开发者_如何学运维ent.

When the problem does occur, I have the Performance Monitor window open and it's not showing anywhere near the limit of the pool (which is 100) - generally around 2 - 5 connections, so I don't think the pool is being exhausted so maybe it's a timeout.

However, I have set the ConnectionTimeout to 0 - which, according to the documentation, means it should wait forever to connect - but I'm not seeing this.

When it does occur, it happens fairly quickly - I'm running under the debugger from VS2010 - starting a new instance of my application - and it might happen within a second or two of starting - in starting up the app there are several queries that happen. The actual SQL Server I'm running against is SQL Express 2008. Maybe I should try running it against SQL Server 2008 and see if I see any different behaviour.

Any other ideas?

Take a look at the ADO.NET Performance Counters related to pooling.

Your described symptom is often an indication that you are leaking connections. Make sure all connections are disposed when you are finished with them, preferably by wrapping in an using statement.

here's some code to try the pool and then failover to unpooled: use this sub if a problem happens with the pool:

Public Sub OpenConn()
    Dim sTempCNString As String = cn.ConnectionString

    Try
        ' add a timeout to the cn string, following http://www.15seconds.com/issue/040830.htm
        Dim iTimeOut As Integer = utils_Configuration.Get_ConfigInt("DBConnectTimeout", 0)
        If (iTimeOut > 0 And Not cn.ConnectionString.ToLower.Contains("timeout")) Then
            Diagnostics.Debug.Print("<><><><><><><> SHORT CONNECT WITH POOLING <><><><><><><><><> ")
            cn.ConnectionString += ";Connect Timeout=" & iTimeOut.ToString() & ";"
        End If

        cn.Open()
        IsOperational = True
    Catch ex As Exception
        Diagnostics.Debug.Print("ERROR IN OPENING, try no pool")
        ' see http://www.15seconds.com/issue/040830.htm
        ' turn off pooling
        Diagnostics.Debug.Print("<><><><><><><> CONNECT WITHOUT POOLING <><><><><><><><><> ")
        Dim sAddOn As String = ";Pooling=false;Connect Timeout=45;"
        cn.ConnectionString = sTempCNString & sAddOn
        cn.ConnectionString = cn.ConnectionString.Replace(";;", ";")
        cn.Open()
    End Try
End Sub

Here's some code to monitor the pool:

Option Explicit On
Option Strict On

Imports System.Data.SqlClient
Imports System.Diagnostics
Imports System.Runtime.InteropServices
Imports Microsoft.VisualBasic


' ref: http://msdn2.microsoft.com/en-us/library/ms254503.aspx

Public Class utils_SqlPerfMon

    Private PerfCounters(9) As PerformanceCounter
    Private connection As SqlConnection
    Public sConnectString As String = ""
    Public sResult As String = ""

    Public Sub New()
        sConnectString = Tools.GetMainDBConn().ConnectionString
        connection = New SqlConnection(sConnectString)
        Exec()
    End Sub

    Public Sub New(ByVal strC As String)
        sConnectString = strC
        connection = New SqlConnection(sConnectString)
        Exec()
    End Sub

    Public Sub Exec()

        Me.SetUpPerformanceCounters()
        Diagnostics.Debug.Print("Available Performance Counters:")

        ' Create the connections and display the results.
        Me.CreateConnectionsAndDisplayResults()

    End Sub

    Private Sub CreateConnectionsAndDisplayResults()
        ' List the Performance counters.
        WritePerformanceCounters()

        Dim connection1 As SqlConnection = New SqlConnection( _
           Me.sConnectString)
        connection1.Open()

        Diagnostics.Debug.Print("Opened the 1st Connection:")
        WritePerformanceCounters()

        connection1.Close()
        Diagnostics.Debug.Print("Closed the 1st Connection:")
        WritePerformanceCounters()


        Return


    End Sub

    Private Enum ADO_Net_Performance_Counters
        NumberOfActiveConnectionPools
        NumberOfReclaimedConnections
        HardConnectsPerSecond
        HardDisconnectsPerSecond
        NumberOfActiveConnectionPoolGroups
        NumberOfInactiveConnectionPoolGroups
        NumberOfInactiveConnectionPools
        NumberOfNonPooledConnections
        NumberOfPooledConnections
        NumberOfStasisConnections
        ' The following performance counters are more expensive to track.
        ' Enable ConnectionPoolPerformanceCounterDetail in your config file.
        '     SoftConnectsPerSecond
        '     SoftDisconnectsPerSecond
        '     NumberOfActiveConnections
        '     NumberOfFreeConnections
    End Enum

    Private Sub SetUpPerformanceCounters()
        connection.Close()
        Me.PerfCounters(9) = New PerformanceCounter()

        Dim instanceName As String = GetInstanceName()
        Dim apc As Type = GetType(ADO_Net_Performance_Counters)
        Dim i As Integer = 0
        Dim s As String = ""
        For Each s In [Enum].GetNames(apc)
            Me.PerfCounters(i) = New PerformanceCounter()
            Me.PerfCounters(i).CategoryName = ".NET Data Provider for SqlServer"
            Me.PerfCounters(i).CounterName = s
            Me.PerfCounters(i).InstanceName = instanceName
            i = (i + 1)
        Next
    End Sub

    Private Declare Function GetCurrentProcessId Lib "kernel32.dll" () As Integer

    Private Function GetInstanceName() As String
        'This works for Winforms apps. 
        'Dim instanceName As String = _
        '   System.Reflection.Assembly.GetEntryAssembly.GetName.Name

        ' Must replace special characters like (, ), #, /, \\ 
        Dim instanceName As String = _
           AppDomain.CurrentDomain.FriendlyName.ToString.Replace("(", "[") _
           .Replace(")", "]").Replace("#", "_").Replace("/", "_").Replace("\\", "_")

        'For ASP.NET applications your instanceName will be your CurrentDomain's 
        'FriendlyName. Replace the line above that sets the instanceName with this: 
        'instanceName = AppDomain.CurrentDomain.FriendlyName.ToString.Replace("(", "[") _
        '    .Replace(")", "]").Replace("#", "_").Replace("/", "_").Replace("\\", "_")

        Dim pid As String = GetCurrentProcessId.ToString
        instanceName = (instanceName + ("[" & (pid & "]")))
        Diagnostics.Debug.Print("Instance Name: {0}", instanceName)
        Diagnostics.Debug.Print("---------------------------")
        Return instanceName
    End Function

    Private Sub WritePerformanceCounters()
        Dim sdelim As String = vbCrLf  ' "<br>"
        Diagnostics.Debug.Print("---------------------------")
        sResult += "---------------------------"
        sResult += sdelim

        Dim strTemp As String = ""
        For Each p As PerformanceCounter In Me.PerfCounters
            Try
                Diagnostics.Debug.Print("{0} = {1}", p.CounterName, p.NextValue)
                strTemp = p.CounterName & "=" & p.NextValue.ToString
            Catch ex As Exception
                strTemp = ""
            End Try
            sResult += strTemp
            sResult += sdelim
        Next
        Diagnostics.Debug.Print("---------------------------")
        sResult += "---------------------------"
            sResult += sdelim
    End Sub


    Private Shared Function GetSqlConnectionStringDifferent() As String
        ' To avoid storing the connection string in your code, 
        ' you can retrive it from a configuration file. 
        Return ("Initial Catalog=AdventureWorks;Data Source=.\SqlExpress;" & _
          "User Id=LowPriv;Password=Data!05;")
    End Function


End Class