Get Active Transaction Details

Ever wanted to get more detail on active transactions on your SQL Server? Well then, this nifty piece of T-SQL will provide the following information:

  • The size of the transaction log usage and records generated for a particular transaction. So for example, if you ever needed to roll back a transaction, you’ll have a fairly good idea of the effort required e.g. whether the transaction log size is a few 100 MB or a few GB. This is vital information when you’re under pressure to make a decision to kill a process or not.
  • The database the transactions are occurring in e.g. it gives you an idea of tempdb usage of a transaction – the T-SQL below could potentially give you an idea of who or what is filling up your tempdb if you’re quick enough.
  • The T-SQL that is executing and the plan handle so you can potentially get query stats later.
  • The elapsed time of the transaction and its current state and transaction start time (for SQL 2008+)
  • The last request start time, end time, reads, writes, cpu_time – i.e. is it working?
  • The program name, server host name it’s originating from and the process ID.

-- Get Active Transaction Information
-- @niftysql / niftysql.wordpress.com
-- Tip: use in conjunction with https://niftysql.wordpress.com/2016/08/24/get-top-cpu-or-io-spid/

SELECT es.session_id
 ,es.login_name
 ,es.original_login_name
 ,es.host_name
 ,es.host_process_id
 ,es.program_name
 ,er.status
 ,es.login_time
 ,es.last_request_start_time
 ,es.last_request_end_time
 ,es.reads
 ,es.writes
 ,es.logical_reads
 ,tst.transaction_id
 ,tst.open_transaction_count -- Comment this for SQL 2008
 ,DB_NAME(tdt.database_id) AS [database_name]
 ,tdt.database_transaction_begin_time -- Comment this for SQL 2005.
 ,CASE tdt.database_transaction_type
      WHEN 1 THEN 'Read/write transaction'
      WHEN 2 THEN 'Read-only transaction'
      WHEN 3 THEN 'System transaction'
 END AS [database_transaction_type]
 ,CASE tdt.database_transaction_state
      WHEN 1 THEN 'The transaction has not been initialized.'
      WHEN 3 THEN 'The transaction has been initialized but has not generated any log records.'
      WHEN 4 THEN 'The transaction has generated log records.'
      WHEN 5 THEN 'The transaction has been prepared.'
      WHEN 10 THEN 'The transaction has been committed.'
      WHEN 11 THEN 'The transaction has been rolled back.'
      WHEN 12 THEN 'The transaction is being committed. In this state the log record is being generated, but it has not been materialized or persisted.'
 END AS [database_transaction_state]
 ,tdt.database_transaction_log_record_count
 ,tdt.database_transaction_replicate_record_count
 ,tdt.database_transaction_log_bytes_used -- tdt.database_transaction_log_bytes_used/1024/1024/1024 = /KB/MB/GB
 ,tdt.database_transaction_log_bytes_reserved
 ,er.command
 ,er.cpu_time
 ,er.total_elapsed_time
 ,text
 ,er.sql_handle
 ,er.plan_handle
FROM sys.dm_tran_session_transactions tst
JOIN sys.dm_tran_database_transactions tdt
ON tst.transaction_id = tdt.transaction_id
JOIN sys.dm_exec_sessions es
ON tst.session_id = es.session_id
JOIN sys.dm_exec_requests er
ON tst.session_id = er.session_id
CROSS APPLY sys.dm_exec_sql_text (er.sql_handle)
WHERE tdt.database_transaction_begin_time IS NOT NULL
-- AND DB_NAME(tdt.database_id) = 'tempdb' -- E.g: filter only by tempdb.
-- AND tdt.database_transaction_log_bytes_used > 100000000 -- E.g: filter only transaction log usage > 100MB
-- ORDER BY tdt.database_transaction_log_bytes_use DESC -- Order by most transaction using the most log.
OPTION (MAXDOP 1);
Advertisements