Lesson Learned #7: Monitoring the transaction log space of my database
Published Mar 13 2019 06:15 PM 3,158 Views

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

 

Version history
Last update:
‎Oct 10 2019 12:57 AM
Updated by: