All connections in pool are in use

We currently have a little situation on our hands - it seems that someone, somewhere forgot to close the connection in code. Result is that the pool of connections is relatively quickly exhausted. As a temporary patch we added Max Pool Size = 500; to our connection string on web service, and recycle pool when all connections are spent, until we figure this out.

So far we have done this:

SELECT SPId
FROM MASTER..SysProcesses
WHERE DBId = DB_ID('MyDb')  and last_batch < DATEADD(MINUTE, -15, GETDATE())

to get SPID's that aren't used for 15 minutes. We're now trying to get the query that was executed last using that SPID with:

DBCC INPUTBUFFER(61)

but the queries displayed are various, meaning either something on base level regarding connection manipulation was broken, or our deduction is erroneous...

Is there an error in our thinking here? Does the DBCC / sysprocesses give results we're expecting or is there some side-effect catch? 开发者_C百科(for example, connections in pool influence?)

(please, stick to what we could find out using SQL since the guys that did the code are many and not all present right now)

I would expect that there is a myriad of different queries 'remembered' by inputbuffer - depending on the timing of your failure and the variety of queries you run, it seems unlikely that you'd see consistent queries in this way. Recall that the connections will eventually be closed, but only when they're GC'd and finalized.

As Mitch suggests, you need to scour your source for connection-opens and ensure they're localized and wrapped in a using(). Also look for possibly-long-lived objects that might be holding on to connections. In an early version of our catalog ASP page objects held connections that weren't managed properly.

To narrow it down, can you monitor connection-counts (perfmon) as you focus on specific portions of your app? Does it happen more in CRUD areas vs. reporting or other queries? That might help narrow down the source-scour you need to do.

Are you able to change the connection strings to contain information about where and why the connection was created in the Application field?