Finding Out the Connection Leaks in Sql Server

This Query (Q1) will show the connections that are in sleep mode
e.g. the datareaders associated with this Query might be not closed

(Q1)


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'

Q2 will show the number of open connections by the Database Users


(Q2 )



SELECT DB_NAME(dbid) as 'DataBase Name',
COUNT(dbid) as 'Total Connections'
FROM master.dbo.sysprocesses WITH (nolock)
WHERE dbid > 0
GROUP BY dbid