In general if you hit the limit for the pool, something is keeping the connections opened. It could be pending distributed transactions as well, double check this.
One trick I did to track this one down is capture stack trace for each connection opened and store in hash table, then remove from hash table right when connection is disposed. Then you can dump the connections and stack traces when you hit the limit to see what functions are causing the problem. I used this with ASP.NET code where it has to be thread safe, etc... so it should work anywhere.
If you don't want to go down this route, a simpler method would be to dump out stack trace and SPID of connection right after connection is opened. Then when the problem occurs run "select * from master..sysprocesses" to get list of opened SPIDs and use this with your stack trace log to see what stack traces are leaving opened connections.
Note I'm not sure if this code works with .NET 2.0 however!
using System;
using System.Collections;
using System.Reflection;
using System.Text;
namespace ConnTrack
{
public class ConnTrack
{
// Initial stack frames to skip when walking stack.
public const int STACK_SKIP_FRAMES = 1;
// Maximum stack depth to capture.
public const int MAX_STACK_DEPTH = 20;
// This is just handy constant to save or-ing all these BindingFlags values over and over in my code.
public const BindingFlags BINDING_FLAGS = (BindingFlags.DeclaredOnly|BindingFlags.Public|BindingFlags.NonPublic|BindingFlags.Instance|BindingFlags.GetField);
// Main internal cconnection hash table.
public static Hashtable connectionHash = new Hashtable();
// Current maximum stack string size (dynamically grows to accomodate largest stack).
public int maxStackStringSize = 1000;
// Failure counters so we can examine if class is having problems.
public int failedNullConnection = 0;
public int failedNullInternalConnection = 0;
public int failedStackWalk = 0;
public int failedSaveToHashConnection = 0;
public int failedTrackOpenConnection = 0;
public int failedTrackDisposeConnection = 0;
// Success counters to track open and closes.
public int successTrackOpenConnection = 0;
public int successTrackDisposeConnection = 0;
// External callers call this function after opening a connection.
// Function captures stack trace and stores this in the connectionHash hash table
// using the hashcode of the _internalConnection member variable of the connection.
// Works with both SqlConnection and OracleConnection objects.
public void TrackOpenConnection( object conn )
{
int i, maxStackDepth, icHashCode;
System.Diagnostics.StackTrace stack;
System.Diagnostics.StackFrame frame;
StringBuilder sb;
// Check for null input, track errors.
if ( null == conn )
{
failedNullConnection++;
failedTrackOpenConnection++;
return;
}
// Capture stack trace for connection.
try
{
// Get connection's internalConnection hash code.
// This will return 0 on failure.
icHashCode = GetInternalConnectionHashCode( conn );
if ( 0 == icHashCode )
{
failedTrackOpenConnection++;
return;
}
stack = new System.Diagnostics.StackTrace( STACK_SKIP_FRAMES, true );
sb = new StringBuilder( 1000 );
sb.Append( "c.hc=" );
sb.Append( conn.GetHashCode().ToString() );
sb.Append( "ic.hc=" );
sb.Append( icHashCode.ToString() );
sb.Append( " ticks=" );
sb.Append( System.Environment.TickCount.ToString() );
sb.Append( " tid=" );
sb.Append( System.AppDomain.GetCurrentThreadId().ToString() );
sb.Append( "<br>" );
maxStackDepth = ( stack.FrameCount > MAX_STACK_DEPTH ) MAX_STACK_DEPTH : stack.FrameCount;
for ( i=0; i<maxStackDepth; i++ )
{
frame = stack.GetFrame(i);
if ( null != frame )
{
sb.Append( '[' );
sb.Append( i.ToString() );
sb.Append( "]{" );
sb.Append( frame.GetMethod().Name );
sb.Append( '|' );
sb.Append( frame.GetFileName() );
sb.Append( '|' );
sb.Append( frame.GetFileLineNumber().ToString() );
sb.Append( '}' );
sb.Append( "<br>" );
}
}
}
catch ( Exception ex )
{
string s = ex.Message;
failedStackWalk++;
failedTrackOpenConnection++;
return;
}
// Ok if we get this far, save off stack info to hash table.
lock( connectionHash )
{
try
{
// Add stack to hash table.
connectionHash[icHashCode] = sb.ToString();
// Bump up string builder size to accomodate worst case stack.
// This will also server to grainularize allocations and improve performance.
if ( sb.Length > maxStackStringSize ) maxStackStringSize = sb.Length;
// Track successes.
successTrackOpenConnection++;
}
catch ( Exception )
{
failedSaveToHashConnection++;
failedTrackOpenConnection++;
}
}
}
// Caller calls this function prior to Closing/Disposing connection to remove the connection
// from the stack tracking hash table.
public void TrackDisposeConnection( object conn )
{
int icHashCode;
try
{
icHashCode = GetInternalConnectionHashCode( conn );
if ( 0 == icHashCode )
{
failedTrackDisposeConnection++;
return;
}
lock( connectionHash )
{
connectionHash.Remove( icHashCode );
successTrackDisposeConnection++;
}
}
catch ( Exception )
{
failedTrackDisposeConnection++;
}
}
// Returns stack information in string format for specific connection.
public string GetStackInformation( object conn )
{
int icHashCode;
string s;
try
{
icHashCode = GetInternalConnectionHashCode( conn );
if ( 0 == icHashCode ) return "";
lock( connectionHash )
{
s = (string) connectionHash[icHashCode];
}
return s;
}
catch ( Exception )
{
return "";
}
}
// Returns a string array with all of the currently opened connection stacks.
public string [] GetAllOpenConnectionStacks()
{
int i;
string [] s = null;
ArrayList arrValues = null;
try
{
lock (connectionHash)
{
s = new string[connectionHash.Count];
arrValues = new ArrayList(connectionHash.Values);
for ( i=0; i<arrValues.Count; i++ )
{
s = (string) arrValues;
}
}
return s;
}
catch ( Exception )
{
return null;
}
}
// Internal helper function to get hash code of connection's internalConnection member variable.
private int GetInternalConnectionHashCode( object conn )
{
Type t;
object oic;
// Get internal connection object handle.
try
{
if ( null == conn )
{
failedNullInternalConnection++;
return 0;
}
t = conn.GetType();
if ( null == t )
{
failedNullInternalConnection++;
return 0;
}
// Cool thing about this is it works for both SqlConnection and OracleConnection classes.
// Both of these classes use _internalConnection member variable to store true connection object.
oic = t.InvokeMember( "_internalConnection", BINDING_FLAGS, null, conn, null );
if ( null == oic )
{
failedNullInternalConnection++;
return 0;
}
// Return hashcode.
return oic.GetHashCode();
}
catch( Exception )
{
failedNullInternalConnection++;
return 0;
}
}
}
}
| 
