How to solve the problem with the connection pool?

Question

Recently, I began to encounter problems connecting to a database with SQL Server on my development machine.

System.InvalidOperationException: Timed out. Waiting period before receiving a connection from the pool

How can I control the connection pool to find out what is happening?

Additional Information:

I was not lucky with this - I am definitely not leaking connections. Each connection is inside a using statement.

When the problem occurs, I have the Performance Monitor window open and it does not appear anywhere near the pool limit (which is 100) - usually about 2 - 5 connections, so I don’t think the pool is exhausted, so maybe this is a timeout .

However, I set ConnectionTimeout to 0 - which, according to the documentation, means that it has to wait forever to connect, but I don't see it.

When this happens, it happens pretty quickly - I start under the debugger from VS2010 - launch a new instance of my application - and this can happen within a second or two from launch - when the application starts, there are several requests that occur. The actual SQL Server I came across is SQL Express 2008. Maybe I should try running it with SQL Server 2008 and see if I see any other behavior.

Any other ideas?

Answer 1

Take a look at the ADO.NET pool performance meters .

Your symptom described is often a sign that you are experiencing a leak. Make sure all connections are located when you are done with them, preferably by wrapping in a using statement.

Answer 2

here is some code to try the pool and then reset to subcooling: use this subsection if there is a problem 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 is some code to control 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 '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