SQL Server - How to find Open Transactions

gistfile1.sql

--open transactions with text and plans
--session ID
--login name
--database context
--transaction begin time
--how many log records have been generated by the transaction
--how much log space has been taken up by those log records
--how much log space has been reserved in case the transaction rolls back
--the last T-SQL that was executed in the context of the transaction
--the last query plan that was executed (only for currently executing plans)


 BEGIN TRAN 
 ROLLBACK TRAN 
 
SELECT DB_NAME(DT.database_id)      AS DBName,
       TranType      = CASE DT.database_transaction_type
                       WHEN 1 THEN 'Read/write transaction'
                       WHEN 2 THEN 'Read-only transaction'
                       WHEN 3 THEN     'System transaction'
                  END,
       TranState     = CASE database_transaction_state
                        WHEN 1 THEN 'Transaction has not been initialized'
                        WHEN 3 THEN 'Transaction has been initialized but has not generated any log records'
                        WHEN 4 THEN 'Transaction has generated log records'
                        WHEN 5 THEN 'Transaction has been prepared'
                        WHEN 10 THEN 'Transaction has been committed'
                        WHEN 11 THEN 'Transaction has been rolled back'
                        WHEN 12 THEN 
                             'Transaction is being committed. In this state the log record is being generated, but it has not been materialized or persisted'
                   END,
       *
FROM   sys.dm_tran_database_transactions DT
       LEFT JOIN sys.dm_tran_session_transactions ST
            ON  ST.transaction_id = DT.transaction_id
       LEFT JOIN sys.dm_exec_sessions S
            ON  S.session_id = ST.session_id
            
            
SET NOCOUNT ON

GO

/*-----------------------------------------------------------------------------------------*/



-------------- find sql transactions with large logs
--database_transaction_log_record_count – Number of log records for the transaction
--database_transaction_replicate_record_count – Number of log records that will be replicated
--database_transaction_log_bytes_reserved – Log space reserved by the transaction
--database_transaction_log_bytes_used – Log space used by the transaction
--database_transaction_log_bytes_reserved_system – Log space reserved by system on behalf of the transaction
--database_transaction_log_bytes_used_system – Log space used by system on behalf of the TRANSACTION

DECLARE @datetime DATETIME

SELECT @datetime = GETDATE()

SELECT @datetime logtime,
       TEXT,
       tr.database_id,
       tr.transaction_id,
       database_transaction_log_bytes_used,
       database_transaction_log_bytes_reserved,
       database_transaction_log_record_count,
       database_transaction_state,
       database_transaction_status,
       database_transaction_log_bytes_used_system,
       database_transaction_log_bytes_reserved_system
FROM   sys.dm_tran_database_transactions tr
       INNER JOIN sys.dm_exec_requests r
            ON  tr.transaction_id = r.transaction_id
       CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE  database_transaction_log_bytes_used > 100 * 1024 * 1024 -- 100 MB

/*-----------------------------------------------------------------------------------------*/


-------------- find sql transactions with large logs in user Database 
SELECT transaction_id,
	 DB_NAME(database_id) AS DBName,
	 database_transaction_begin_time,
	 CASE database_transaction_type 
        WHEN 1 THEN 'Read/Write' 
        WHEN 2 THEN 'Read-Only' 
        WHEN 3 THEN 'System'
        ELSE 'Unknown Type - ' + convert(VARCHAR(50), database_transaction_type) 
    END AS TranType, 
    CASE database_transaction_state 
		  WHEN 1 THEN 'Uninitialized'
		  WHEN 3 THEN 'Not Started'
		  WHEN 4 THEN 'Active'
		  WHEN 5 THEN 'Prepared'
		  WHEN 10 THEN 'Committed'
		  WHEN 11 THEN 'Rolled Back'
		  WHEN 12 THEN 'Comitting'
        ELSE 'Unknown State - ' + convert(VARCHAR(50), database_transaction_state) 
    END AS TranState,
	database_transaction_log_record_count AS LogRecords,
	database_transaction_replicate_record_count AS ReplLogRcrds,
	database_transaction_log_bytes_reserved/1024.0 AS LogResrvdKB,
	database_transaction_log_bytes_used/1024.0 AS LogUsedKB,
	database_transaction_log_bytes_reserved_system/1024.0 AS SysLogResrvdKB,
	database_transaction_log_bytes_used_system/1024.0 AS SysLogUsedKB
FROM sys.dm_tran_database_transactions
WHERE database_id NOT IN (1, 2, 3, 4, 32767)