How to track database connection leaks

Question

We have an app which seems to have connection leaks (SQL Server says that the max pool size has been reached). I am alone on my dev machine (obviously), and just by navigating the app, I trigger this error. The SQL Server Activity monitor shows a great number of processes using my database.

I want to find which files open connections but do not use it. I was thinking of using something like grep to, for each file, count the number of ".Open()" and the number of ".Close()", and get the file for which the numbers are not equal. Is it realistic?

Bonus question: do the processes found in SQL Server Activity Monitor correspond to the connections? If not, how do I find out how many connections are open on my database?

The app is in asp.net (vb) 3.5, with SQL Server 2005. We currently do not use LINQ (yet) or anything like that.

Thanks

Answer 1

When looking at the code from the SQL Server side you can run the following query to get a view on which queries are last run on sleeping connections. (open connections which are doing nothing)

SELECT ec.session_id, last_read, last_write, text, client_net_address, program_name, host_process_id, login_name
FROM sys.dm_exec_connections  ec
JOIN sys.dm_exec_sessions es
  ON ec.session_id = es.session_id
CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) AS dest
where es.status = 'sleeping'

From the application side you can debug with sos.dll as described in the following articles:

• How to troubleshoot leaked SqlConnection Objects Part 1
• How to troubleshoot leaked SqlConnection Objects Part 2

If you need more information on how to use windbg, these articles are a good intro:

• Getting Started with WinDBG Part 1
• Getting Started with WinDBG Part 2