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
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