I'm running into a very odd problem. We're using Web Api 2.1 with the Enterprise Library 6 data block and dependency injection through Unity. I started noticing that if I refreshed one of our pages a lot, that eventually I'd get the dreaded "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool" error. There are only about 6 web api calls on the page.
Presumably Enterprise Library should be disposing of my connections for me, so I'm wondering if it has anything to do with our use of DI and the lifetime managers? We've checked our code, and we have "using" blocks everywhere expected.
Anyway, what I've seen from looking around:
Examining Sql Server sysprocesses shows connections from our site to be fairly low (maxes out at about 8). I do occasionally see connections with our program name with empty loginame AND nt_username fields. I'm not sure what that's about. Those spike up to 20 connections, but also tend to disappear very rapidly (within seconds).
However, examining the counters for .NET Data Provider for SqlServer in PerfMon for our web api site shows a very rapid climb for Number of Pooled Connections. The aforementioned page will hit 120~ connections pretty fast after several refreshes. Number of Reclaimed Connections stays at 0 for quite some time. Eventually, it will spike up after a long period of inactivity. I've read that's indicative of improperly closed connections.
Once 120~ pooled connections are reached, the api site will perpetually return 500 errors with the connection pool error, even if the connections on Sql Server are relinquished, which happens far, far before IIS shows any reclaimed connections. Sql Server relinquishes after maybe 5 minutes of inactivity, whereas IIS will after 30~.
Well, this is going to be disappointing. It ended up being an improper dispose in one of our libraries.
I had checked all my repository classes that I was using, but I hadn't checked our data framework, which had an implementation of IResultSetMapper that wasn't closing the data reader.