how to debug a leaking connection pool (question)

[Jan Limpens]

hello,

recently under not yet clearly defined circumstances (it isn't  load), we sometimes get into the situation of a non-reacting sql server with the thrown exception:

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.

In the connection string we define the pool with min 4 and max 100 - by the dimensions of the website suffering from this condition, this should be sufficient. Seems to me, something opens connections, but does not release them - but I have no real good idea, on how to debug this (especially as this shows only on the production server, as usual), to find the evil doer.

Anyone could give me some hints, on how to approach this?

We are using nh 2.1 with the rhino.commons base application for session management.

--
Jan

[jeffry...@gmail.com]

Sounds like dispose/close is not being called on a session somewhere. I have had the same error when not closing ado.net connections in the past...

Sent from my Verizon Wireless BlackBerry

---

From: Jan Limpens <jan.l...@gmail.com>

Date: Wed, 30 Dec 2009 23:43:42 +0100

To: nhusers<nhu...@googlegroups.com>

Subject: [nhusers] how to debug a leaking connection pool (question)

--

You received this message because you are subscribed to the Google Groups "nhusers" group.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.

[Jan Limpens]

Yes, exactly. But in this case nh and rhino commons should manage this - and I assume they do this correctly.

So my suspicion is, that some transient services holding a reference to an ISession, do not get disposed as quickly as I would want them to (Castle Windsor contains them) and thus clobber up the connection pool. But this is all theory.

What would be interesting for me to know is how you guys would debug such a situation.

2009/12/31 <jeffry...@gmail.com>

--
Jan

[Harm Neervens]

Hi Jan,

We had the same problem in a live production environment (ASP.NET).
I've build a service in the past that monitors the connections that
are pooled within ASP.NET.
Because all the connection pool stuff is private or internal, you have
to do a lot of reflection stuff on private members.
Here is the code of the main method I wrote for it. Hope you can use
it:

private void UpdatePoolTable()
{
SqlConnection conn = new SqlConnection
(ApplicationSettings.ConnectionString);

conn.Open();

try
{
object factory = conn.GetConnectionFactory();

IDictionary allPoolGroups =
conn.GetPoolGroupsFromFactory();

int count = allPoolGroups.Count;

foreach (DictionaryEntry entry in allPoolGroups)
{
object poolGroup = entry.Value;

HybridDictionary poolCollection =
Typhone.Reflection.ReflectionHelper.GetFieldValue(poolGroup,
"_poolCollection") as HybridDictionary;

foreach (DictionaryEntry poolEntry in
poolCollection)
{

object foundPool = poolEntry.Value;

FieldInfo _objectListFieldInfo =
foundPool.GetType().GetField("_objectList", BindingFlags.NonPublic |
BindingFlags.Instance);

object listTDbConnectionInternal =
Typhone.Reflection.ReflectionHelper.GetFieldValue(foundPool,
"_objectList");

MethodInfo get_CountMethodInfo =
listTDbConnectionInternal.GetType().GetMethod("get_Count");

//object numConnex =
get_CountMethodInfo.Invoke(listTDbConnectionInternal, null);
object numConnex =
ReflectionHelper.InvokeMethod(listTDbConnectionInternal, "get_Count");
//MessageBox.Show(entry.Key.ToString() + ":
" + numConnex.ToString(), "Number of Physical DB Conns open");
FormFunctions.AddRowToTable(tablePoolStats,
entry.Key.ToString() + ": " + numConnex.ToString(), "");

}

}

}
finally
{
conn.Close();
conn.Dispose();
}
}

Best regards,
Harm.

[Jan Limpens]

Hi Harm,

this is quite along the lines of the thing I imagined I would need!
Thanks a lot, this saves us quite a bit of time for sure!

Jan

2010/1/2 Harm Neervens <h.nee...@gmail.com>

--

You received this message because you are subscribed to the Google Groups "nhusers" group.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.

--
Jan

[Davy Brion]

if you're using windsor, you have to make absolutely sure that every transient component resolved through Windsor is released explicitly (through the container's Release method).

Transient dependencies of transient resolved components will be disposed of immediately once the resolved component is released through the container.  If you don't do that, they will actually never be disposed and will remain in memory forever.

[Davy Brion]

more specifically: search for places where the resolve method is called without a try/finally.  the release method should be called in the finally block

or you can replace your calls to resolve with a Resolvable class like this:
http://davybrion.com/blog/2008/12/the-resolvable/

i've been bitten by this myself a couple of times ;)