We presently possess a little situation on our hands - it appears that somebody, somewhere didn't remember to shut the bond in code. Result would be that the pool of connections is comparatively rapidly exhausted. Like a temporary patch we added
Max Pool Size = 500; to the connection string on web service, and recycle pool when all connections are spent, until we figure this out.
To date we've carried this out:
SELECT SPId FROM MASTER..SysProcesses WHERE DBId = DB_ID('MyDb') and last_batch < DATEADD(MINUTE, -15, GETDATE())
to obtain SPID's that are not employed for fifteen minutes. We are now looking to get the query which was performed last by using their SPID with:
however the queries displayed are various, meaning either something on base level regarding connection manipulation was damaged, or our deduction is erroneous...
Can there be a mistake within our thinking here? Does the DBCC / sysprocesses give results we are expecting or perhaps is there some side-effect catch? (for instance, connections in pool influence?)
(please, stay with what we should could discover using SQL because the men that did the code are lots of and never all present at this time)
I'd expect that there's an array of different queries 'remembered' by inputbuffer - with respect to the timing of the failure and all of the queries you take, it appears unlikely that you would see consistent queries in by doing this. Recall the connections will ultimately be closed, only when they are GC'd and completed.
As Mitch indicates, you have to scour your source for connection-opens and be sure they are localized and covered with a using(). Also search for possibly-lengthy-resided objects that could be holding onto connections. Within an early version in our catalog ASP page objects held connections that were not handled correctly.
To narrow it lower, are you able to monitor connection-counts (perfmon) as you concentrate on specific servings of your application? Will it happen more in CRUD areas versus. confirming or any other queries? That can help limit the origin-scour you must do.
Is it possible to alter the connection strings to contain details about where and why the bond was produced within the Application area?