First published on MSDN on Oct 31, 2016
In many support cases, our customers want to monitor the available space for the transaction log space for their database or to know what caused an error when the transaction is full.
Unfortunately, there is not supported to use DBCC SQLPERF(LOGSPACE) nowdays. For this reason, I would like to share with you another view where you could find out these results.
Running the below query you could find all transactions active, the users that are executing them , TSQL statement, etc..
SELECT TranSession.[session_id], SessionS.[login_name] AS [Login Name], TranDB.[database_transaction_begin_time] AS [Start_Time], CASE TranActive.transaction_type WHEN 1 THEN 'Read/write transaction' WHEN 2 THEN 'Read-only transaction' WHEN 3 THEN 'System transaction' END AS [Transaction_Type], CASE TranActive.transaction_state WHEN 1 THEN 'The transaction has not been initialized' WHEN 2 THEN 'The transaction is active' WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions' WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.' WHEN 6 THEN 'The transaction has been committed' WHEN 7 THEN 'The transaction is being rolled back' WHEN 8 THEN 'The transaction has been rolled back' END AS [Transaction_State], TranDB.[database_transaction_log_record_count] AS [Log_Records], TranDB.[database_transaction_log_bytes_used] AS [Log_Bytes_Used], SQlText.text AS [Last_Transaction_Text], SQLQP.[query_plan] AS [Last_Query_Plan] FROM sys.dm_tran_database_transactions TranDB INNER JOIN sys.dm_tran_session_transactions TranSession ON TranSession.[transaction_id] = TranDB.[transaction_id] INNER JOIN sys.dm_tran_active_transactions TranActive ON TranSession.[transaction_id] = TranActive.[transaction_id] INNER JOIN sys.dm_exec_sessions SessionS ON SessionS.[session_id] = TranSession.[session_id] INNER JOIN sys.dm_exec_connections Connections ON Connections.[session_id] = TranSession.[session_id] LEFT JOIN sys.dm_exec_requests Request ON Request.[session_id] = TranSession.[session_id] CROSS APPLY sys.dm_exec_sql_text (Connections.[most_recent_sql_handle]) AS SQlText OUTER APPLY sys.dm_exec_query_plan (Request.[plan_handle]) AS SQLQP
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.