365DBA Blog

Database Stuff

Transactions With Log Records

Here is another fairly simple SQL Server script to show information on open transactions which have any log records. You can use this to get detailed information about processes that are currently running or schedule it to catch any processes which might be bloating your transaction log (which is hard to catch after the fact if you don't see it right afterward rolling back).

Select GetDate() As recCreatedDt,
DateDiff(ss, DBT.database_transaction_begin_time, GetDate()) As AgeInSeconds,
DB_Name(DBT.database_id) As 'DatabaseName',
DBT.transaction_id,
DBT.database_transaction_begin_time,
Case DBT.database_transaction_type
When 1 Then 'Read/write transaction'
When 2 Then 'Read-only transaction'
When 3 Then 'System transaction'
End As 'TypeOfTransaction',
Case DBT.database_transaction_state
When 1 Then 'The transaction has not been initialized.'
When 3 Then 'The transaction has been initialized but has not generated any log records.'
When 4 Then 'The transaction has generated log records'
When 5 Then 'The transaction has been prepared.'
When 10 Then 'The transaction has been committed.'
When 11 Then 'The transaction has been rolled back.'
When 12 Then 'The transaction is being committed. In this state the log record is being generated, but it has not been materialized or persisted.'
End As 'StateOfTransaction',
DBT.database_transaction_log_record_count As record_count,
DBT.database_transaction_replicate_record_count As replicated_records,
DBT.database_transaction_log_bytes_used As bytes_used,
DBT.database_transaction_log_bytes_reserved As bytes_reserved,
DBT.database_transaction_log_bytes_used_system As system_bytes,
DBT.database_transaction_log_bytes_reserved_system As system_reserved,
--DBT.database_transaction_begin_lsn,
--DBT.database_transaction_last_lsn,
--DBT.database_transaction_most_recent_savepoint_lsn,
--DBT.database_transaction_commit_lsn,
--DBT.database_transaction_last_rollback_lsn,
--DBT.database_transaction_next_undo_lsn, 
ES.[host_name], 
ES.[program_name], 
Case ER.transaction_isolation_level
When 0 Then 'Unspecified'
When 1 Then 'ReadUncomitted'
When 2 Then 'ReadCommitted'
When 3 Then 'Repeatable'
When 4 Then 'Serializable'
When 5 Then 'Snapshot'
End As IsolationLevel, 
ER.command,
S.text, 
SubString(S.text, (ER.statement_start_offset/2)+1, 
((Case ER.statement_end_offset
When -1 Then DataLength(S.text)
Else ER.statement_end_offset
End - ER.statement_start_offset)/2) + 1) As statement_text
From sys.dm_tran_database_transactions DBT
Left Join sys.dm_tran_session_transactions ST On DBT.transaction_id = ST.transaction_id
Left Join sys.dm_exec_sessions ES On ST.session_id = ES.session_id
Left Join sys.dm_exec_requests ER On DBT.transaction_id = ER.transaction_id
Outer Apply sys.dm_exec_sql_text(ER.sql_handle) S
Where DBT.database_transaction_log_record_count > 0
Loading