As a .NET developer using SQL Server exclusively I have found that it is very easy to forget to close connections when using datasets with table adapters or readers. Due to this I needed a way to find the SQL text for the IDLE connections that were orphaned by my application. This script provided 99% of what I needed. THANK YOU!!!! Here is my updated script which also joins the sys.dm_exec_connections view to find the last SQL command run on the connections. In this way I can find the code responsible for the call to SQL and add the required closes for the connections.

SELECT

t.text AS SQLStatement,

s.Session_ID AS SPID,

COALESCE(r.status, s.status) AS Status,

s.login_name AS Login,

s.host_name AS HostName,

lw.BlkBy AS BlockedBy,

DB_NAME(r.Database_ID) AS DBName,

r.command AS Command,

COALESCE(r.cpu_time, s.cpu_time) AS CPUTime,

COALESCE((r.reads + r.writes), (s.reads + s.writes)) AS DiskIO,

s.last_request_start_time AS LastBatch,

s.program_name AS ProgramName

FROM

sys.dm_exec_sessions AS s

LEFT JOIN

sys.dm_exec_requests AS r

ON

s.session_id = r.session_id

LEFT JOIN

sys.dm_exec_connections AS c

ON

s.Session_ID = c.Session_ID

LEFT JOIN

(

SELECT

l.request_session_id AS SPID,

w.blocking_session_id AS BlkBy

FROM

sys.dm_tran_locks as l

INNER JOIN

sys.dm_os_waiting_tasks as w

ON

l.lock_owner_address = w.resource_address

) AS lw

ON

s.Session_ID = lw.SPID

OUTER APPLY

sys.dm_exec_sql_text(COALESCE(r.sql_handle, c.most_recent_sql_handle)) AS t

Thanks again!!!!!