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? (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)
