SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
SELECT s_tst.[session_id],
s_es.[login_name] AS [LoginName],
DB_NAME(s_tdt.database_id) AS [Database],
s_tdt.[database_transaction_begin_time] AS [BeginTime],
s_tdt.[database_transaction_log_record_count] AS [LogRecords],
s_tdt.[database_transaction_log_bytes_used] AS [LogBytes],
s_tdt.[database_transaction_log_bytes_reserved] AS [LogRsvd],
CASE WHEN s_est.[text] <> '' THEN (SELECT 1 AS tag, NULL AS parent, CHAR (13) + s_est.[text] + CHAR (13) AS [Q!1!Q!CDATA]
FOR XML EXPLICIT,
TYPE)
END AS SQLXml,
s_eqp.[query_plan] AS [LastPlan]
FROM sys.dm_tran_database_transactions s_tdt (NOLOCK)
JOIN sys.dm_tran_session_transactions s_tst (NOLOCK) ON s_tst.[transaction_id] = s_tdt.[transaction_id]
JOIN sys.dm_exec_sessions s_es ON s_es.[session_id] = s_tst.[session_id]
JOIN sys.dm_exec_connections s_ec (NOLOCK) ON s_ec.[session_id] = s_tst.[session_id]
LEFT OUTER JOIN sys.dm_exec_requests s_er (NOLOCK) ON s_er.[session_id] = s_tst.[session_id]
CROSS APPLY sys.dm_exec_sql_text(s_ec.[most_recent_sql_handle]) AS s_est
OUTER APPLY sys.dm_exec_query_plan(s_er.[plan_handle]) AS s_eqp
ORDER BY [BeginTime]
GO