Advanced Search

Open transactions with text and plans

/ Published in: SQL
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. SELECT s_tst.[session_id],
  2.    s_es.[login_name] AS [Login Name],
  3.    DB_NAME (s_tdt.database_id) AS [DATABASE],
  4.    s_tdt.[database_transaction_begin_time] AS [BEGIN TIME],
  5.    s_tdt.[database_transaction_log_record_count] AS [Log Records],
  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]
  10. FROM sys.dm_tran_database_transactions s_tdt
  11.    JOIN sys.dm_tran_session_transactions s_tst
  12.       ON s_tst.[transaction_id] = s_tdt.[transaction_id]
  13.    JOIN sys.[dm_exec_sessions] s_es
  14.       ON s_es.[session_id] = s_tst.[session_id]
  15.    JOIN sys.dm_exec_connections s_ec
  16.       ON s_ec.[session_id] = s_tst.[session_id]
  17.    LEFT OUTER JOIN sys.dm_exec_requests s_er
  18.       ON s_er.[session_id] = s_tst.[session_id]
  19.    CROSS APPLY sys.dm_exec_sql_text (s_ec.[most_recent_sql_handle]) AS s_est
  20.    OUTER APPLY sys.dm_exec_query_plan (s_er.[plan_handle]) AS s_eqp
  21. ORDER BY [BEGIN TIME] ASC;
  22. GO

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.