«

Getting SqlConnection Connection Pool Timeouts?

I had an odd problem today that I thought I should share.
I’m testing a Windows Service that I’m writing (in .Net of course) by incrementing the number of requests thrown at it. The service batches requests from external callers, and then submits the batches to a SQL database. At 1000 requests I suddenly started getting a bunch of exceptions:

System.InvalidOperationException: 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.

I double checked my ADO.NET code, and as usual I was using the “using” pattern (no pun intended) in C#:

 using (SqlConnection conn = new SqlConnection(connectionString)) {  }

The C# compiler turns this into:

 try {     conn.Open(); } finally {    if (conn != null) conn.Close(); }

By placing the call to SqlConnection.Close into the finally block, we ensure that no matter what happens in the try block that SqlConnection is going to get closed.
Having eliminated that from the picture, I copied the important parts of my code into a simple repro. Guess what? No exception! I immediately turned a skeptical eye on my biggest ally, Visual Studio .Net.
Doh! Last night before leaving work I’d set up my machine to enable Sql debugging. The stored procedure which accepts the xml batches was giving me trouble and I decided to try debugger magic rather than my tried and true technique of littering the sproc with PRINT statements. I wasn’t able to get the SQL debugging functionality to work, and I finally squashed the bug the old fashioned way. I never thought to disable SQL debugging on the projects involved.
Well, I did it after becoming suspicious and the mystery exceptions are gone. Which means I don’t have to stay at work late trying to debug. Yeah!