open transactions sqlskills

1SELECT
2    [s_tst].[session_id],
3    [s_es].[login_name] AS [Login Name],
4    DB_NAME (s_tdt.database_id) AS [Database],
5    [s_tdt].[database_transaction_begin_time] AS [Begin Time],
6    [s_tdt].[database_transaction_log_bytes_used] AS [Log Bytes],
7    [s_tdt].[database_transaction_log_bytes_reserved] AS [Log Rsvd],
8    [s_est].text AS [Last T-SQL Text],
9    [s_eqp].[query_plan] AS [Last Plan]
10FROM
11    sys.dm_tran_database_transactions [s_tdt]
12JOIN
13    sys.dm_tran_session_transactions [s_tst]
14ON
15    [s_tst].[transaction_id] = [s_tdt].[transaction_id]
16JOIN
17    sys.[dm_exec_sessions] [s_es]
18ON
19    [s_es].[session_id] = [s_tst].[session_id]
20JOIN
21    sys.dm_exec_connections [s_ec]
22ON
23    [s_ec].[session_id] = [s_tst].[session_id]
24LEFT OUTER JOIN
25    sys.dm_exec_requests [s_er]
26ON
27    [s_er].[session_id] = [s_tst].[session_id]
28CROSS APPLY
29    sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est]
30OUTER APPLY
31    sys.dm_exec_query_plan ([s_er].[plan_handle]) AS [s_eqp]
32ORDER BY
33    [Begin Time] ASC;
34GO
35