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'
