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

The MSDN reference about (ADO.NET performance counters) is pretty clear about what you can look for when profiling the application. You can monitor the counters using the perfmon application built into Windows.

Other than that, I'd suggest learning about ADO.NET connection pooling. If you really suspect a bug in their code, you can take a look at it using Red Gate's Reflector (free for now) which disassembles the MSIL into C#.

You can always check the connection strings from web.config (mainly if they have connection pooling activated, if they have any connection limits enabled).

Also, if you are using IIS 6, you could set your web application to use a separate Application pool, and set other option for the recycling of the memory and processes.

About the performance counters, you could check how long the garbage collector is running, how much memory the application is using, etc.

If you have access to sql server, you could monitor the connections made from your application (there are performance counters defined for every installed instance of SQL Server).

There were some articles in MSDN Magazine. Also you could use the SOS Debugging library to attach to the application's process and check it manually.

And, if you don't have the source code, try to use Reflector to get the sources of the application (they would be very usefull for debugging)

@Later edit:You could check this question here on stackoverflow.com too

I faced this problem and found SQL Server Profiler to be a great tool, I monitored the site in a short testing run and noticed lots of connections being created (sp_who) that were not reused by Connection Pool, so I just opened SQL Server Profiler and then check if all calls to SP made from code were followed by a "sp_reset_connection" call. If the call is not there before the start of a new batch you are just lacking the first connection.

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