/* This Transact-script lists all active transactions with their request data like transaction start, effected rows, bytes to log and wait types to analyse current activities. With this script you can - see all currently running transcations with detailed data - see the current sql statement executing in this transaction - watch the progress & activities of long running DML statement and the amount of data written to log - find long active transaction - see which transactions starts when you run system command like DBCC UPDATEUSAGE , SHRINKDATABASE or INDEXDEFRAG
Works with SQL Server 2005 and higher versions in all editions. Requires VIEW SERVER STATE permission on the server.
-- Active Transactions with Request Data SELECT CASE WHEN TDT.database_id = 32767 THEN 'MSSQLSystemResource' ELSE DB.name END AS DatabaseName ,REQ.start_time AS ReqStart ,TAT.transaction_begin_time AS TransBegin ,TAT.name AS TransName ,CASE TDT.database_transaction_type WHEN 1 THEN N'Read/Write' WHEN 2 THEN N'Read-only' WHEN 3 THEN N'System' ELSE N'Unkown' END AS TransType ,CASE TAT.transaction_state WHEN 0 THEN N'Not initialized' WHEN 1 THEN N'Not started' WHEN 2 THEN N'Active' WHEN 3 THEN N'Ended' WHEN 4 THEN N'DTC active' WHEN 5 THEN N'Preparing' WHEN 6 THEN N'Committing' WHEN 7 THEN N'Being rolled back' WHEN 8 THEN N'Rolled back' ELSE N'Unkown' END AS TransState ,REQ.[status] AS ReqStatus ,TDT.database_transaction_log_record_count AS LogRec ,TDT.database_transaction_log_bytes_used AS LogBytes ,SES.login_name AS LoginName ,REQ.wait_type AS ReqWaitType ,REQ.percent_complete AS [ReqCompl%] ,REQ.command AS ReqCommand ,SUBSTRING(EST.text ,1 + REQ.statement_start_offset / 2 ,(CASE WHEN REQ.statement_end_offset = -1 THEN LEN(convert(nvarchar(max), EST.text)) * 2 ELSE REQ.statement_end_offset END - REQ.statement_start_offset) / 2 ) AS SqlStatement FROM sys.dm_tran_active_transactions AS TAT INNER JOIN sys.dm_tran_database_transactions AS TDT ON TAT.transaction_id = TDT.transaction_id INNER JOIN sys.databases AS DB ON TDT.database_id = DB.database_id LEFT JOIN sys.dm_tran_session_transactions AS TST ON TAT.transaction_id = TST.transaction_id LEFT JOIN sys.dm_exec_requests AS REQ ON TAT.transaction_id = REQ.transaction_id LEFT JOIN sys.dm_exec_sessions AS SES ON REQ.session_id = SES.session_id CROSS APPLY sys.dm_exec_sql_text(REQ.sql_handle) AS EST WHERE TAT.transaction_id > 255 -- No system transactions AND ISNULL(REQ.session_id, -1) <> @@SPID -- Exclude my session -- optional filter to exclude transaction in TempDB -- remove it to get all transactions AND TDT.database_id <> DB_ID(N'tempdb') ORDER BY DatabaseName ,TransBegin ,TransName;
