Lesson Learned #7: Monitoring the transaction log space of my database
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],
WHEN 1 THEN 'Read/write transaction'
WHEN 2 THEN 'Read-only transaction'
WHEN 3 THEN 'System transaction'
END AS [Transaction_Type],
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