T-SQL: List transactions & log used

The code below will list the transactions on the instance, and it will also include the size of the log which is used for that transaction.
The output is ordered by log size.

Select transaction_id, db_name(database_id)as database_name,
database_transaction_begin_time,
case database_transaction_type 
when 1 then 'Read/Write'
when 2 then 'Read only'
when 3 then 'system transsction' end as transaction_type,
case database_transaction_state
when 1 then 'not initialized'
when 2 then 'init. but no log records'
when 3 then 'log records'
when 4 then 'prepared'
when 5 then 'committed'
when 6 then 'rolled back'
when 7 then 'being committed' end as transaction_state,
database_transaction_log_record_count,
database_transaction_log_bytes_used,
database_transaction_begin_lsn,
database_transaction_last_lsn,
database_transaction_most_recent_savepoint_lsn,
database_transaction_commit_lsn,
database_transaction_last_rollback_lsn,
database_transaction_next_undo_lsn
from sys.dm_tran_database_transactions
where transaction_id>1000
order by database_transaction_log_bytes_used desc

You can also use the below to find more info regarding a transaction

SELECT
trans.session_id AS [SESSION ID],
ESes.host_name AS [HOST NAME],login_name AS [Login NAME],
trans.transaction_id AS [TRANSACTION ID],
tas.name AS [TRANSACTION NAME],tas.transaction_begin_time AS [TRANSACTION 
BEGIN TIME],
tds.database_id AS [DATABASE ID],DBs.name AS [DATABASE NAME]
FROM sys.dm_tran_active_transactions tas
JOIN sys.dm_tran_session_transactions trans
ON (trans.transaction_id=tas.transaction_id)
LEFT OUTER JOIN sys.dm_tran_database_transactions tds
ON (tas.transaction_id = tds.transaction_id )
LEFT OUTER JOIN sys.databases AS DBs
ON tds.database_id = DBs.database_id
LEFT OUTER JOIN sys.dm_exec_sessions AS ESes
ON trans.session_id = ESes.session_id
WHERE ESes.session_id IS NOT NULL
and trans.transaction_id = '31441036661'
Share your love