How to detect SqlServer connection leaks in a ASP.net applications?

Question

I'm currently doing some GUI testing on a ASP.net 2.0 application. The RDBMS is SQL Server 2005. The host is Win Server 2003 / IIS 6.0.

I do not have the source code of the application because it was programmed by an external company who's not releasing the code.

I've noticed that the application performs well when I restart IIS but after some testing, after I have opened and closed my browser for a couple of hours, the application starts to get slower and slower. I was wondering if this behaviour was due to a bad closing connection practice from the programmers : I'm suspecting an open connection leak on the database here.

I guess the .Net garbage collector will eventually close them but... that can take a while, no?

I've got SQL Server Management Studio and I do notice from the activity monitor that there are quite a few connections opened on the database.

From all that's being said above, here are some questions related to the main question :

1. Is there any way to know in SQL Server 2005 if connections are open because they're waiting to be used in a connection pool or if they're open because they are used by an application?

2. Does somone know of good online/paper resources where I could learn how to use performance counters or some other kind of tools to help track down these kind of issues?

3. If performance counters are the best solution, what are the variables that I should watch?

Answer 1

Found this thread researching a similar problem. I came up with the following sql as a good way to debug leaky connections in SQL Server:

SELECT S.spid, login_time, last_batch, status, hostname, program_name, cmd,
(
      select text from sys.dm_exec_sql_text(S.sql_handle)
) as last_sql
FROM sys.sysprocesses S
where dbid > 0
and DB_NAME(dbid) = '<my_database_name>'
and loginame = '<my_application_login>'
order by last_batch asc

What this gives you is all open connections on a particular database and login, along with the last sql executed on that connection, sorted by the time at which that sql was executed.

Because of connection pooling, you can’t just rely on the fact that there are a lot of connections hanging around to tell you that you have a connection leakage, because connection pooling will keep connections around even if they are closed correctly from code. However, if you do have a connection leakage, what you will see is that some connections become “frozen”—they will show up in the above query and the “last_batch” timestamp will never change. The other connections will also hang around, but every time new sql is run on them, the “last_batch” timestamp gets updated. So the effect is that the frozen connections will float to the top of this query.

If you have the source code of the application in question, the fact that this gives you the last sql executed on the orphaned connection is very valuable for debugging.

Note The spelling mistake with 'loginame' (missing 'n') is in the sys.sysprocesses view. The statement above is correct.

loginame nchar(128) Login name.

https://docs.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-sysprocesses-transact-sql?view=sql-server-ver15