SQL Server Connections Leaks

I've came across with an exam question on MCTS data access dump and found this topic, I just googled and thought of writing a blog with what I found.

Connection leak basically happens when we open a connection to the database from our application and forget to close it, or due to some reasons it doesn't get closed.

Symptoms of Connection Leaks :

1. Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
2. SQL Server Does Not Exist Or Access Denied 
3. General Network Error 

If your application is leaking connections to Microsoft SQL Server, you can find the last SQL statement that was run for a connection by running this query

SQL SERVER 2008 R2
SELECT recent.text AS 'Last SQL Statement',
               connection.client_net_address AS 'client IP',
               connection.connect_time AS 'connected since'
FROM sys.dm_exec_connections AS connection
CROSS APPLY sys.dm_exec_sql_text(connection.most_recent_sql_handle) AS recent
ORDER BY connection.connect_time ASC

SQL SERVER 2008
SELECT recent.text AS 'Last SQL Statement',
               connection.client_ip_address AS 'client IP',
              connection.connect_time AS 'connected since'
FROM sys.dm_exec_connections AS connection
CROSS APPLY sys.dm_exec_sql_text(connection.most_recent_sql_handle) AS recent
ORDER BY connection.connect_time ASC

You can use the below code to find out whether your application is explicitly closing or disposing the SQL connections.

 string instanceName = Assembly.GetEntryAssembly().FullName;  
 PerformanceCounter pCount = new PerformanceCounter(".NET Data Provider for Sqlserver",  
                                             "NumberOfReclaimedConnections",  
                                             instanceName,  
                                             true );  
 int leakedConnetionsCount = (int) instanceName.NextValue();  

Read more at this MSDN article