What are the transaction in the log
Often I need or want (it's more a want than a need) to know what are the transactions that are stored in the log. Microsoft provides the dynamic management view: sys.dm_tran_database_transactions that keeps track of the transactions that are stored in the log file. IMHO, the most important columns are:
- database_transaction_log_bytes_used
- database_transaction_log_bytes_used_system
- database_transaction_log_bytes_reserved
- database_transaction_log_bytes_reserved_system
- database_transaction_log_record_count
These figures will tell you how healthy is your throughput and if you should be concerned.
Applies to:
- Microsoft SQL Server 2000
- Microsoft SQL Server 2005
- Microsoft SQL Server 2008
sys.dm_tran_database_transactions
SELECT CAST(DB_NAME(database_id) AS VARCHAR(20)) AS 'Database',
transaction_id AS 'Transaction',
database_transaction_log_bytes_used AS 'Bytes used',
database_transaction_log_bytes_used_system AS 'Bytes used system',
database_transaction_log_bytes_reserved AS 'Bytes reserved',
database_transaction_log_bytes_reserved_system AS 'Bytes reserved system',
database_transaction_log_record_count AS 'Rec count'
FROM sys.dm_tran_database_transactions;
go
transaction_id AS 'Transaction',
database_transaction_log_bytes_used AS 'Bytes used',
database_transaction_log_bytes_used_system AS 'Bytes used system',
database_transaction_log_bytes_reserved AS 'Bytes reserved',
database_transaction_log_bytes_reserved_system AS 'Bytes reserved system',
database_transaction_log_record_count AS 'Rec count'
FROM sys.dm_tran_database_transactions;
go
Database Transaction Bytes used Bytes used system Bytes reserved Bytes reserved system Rec count -------------------- -------------------- -------------------- ----------------- -------------------- --------------------- ----------- tempdb 245 0 0 0 0 0 tempdb 288 0 0 0 0 0 tempdb 237 0 0 0 0 0 tempdb 276 0 0 0 0 0 tempdb 258 0 0 0 0 0 tempdb 270 0 0 0 0 0 tempdb 292 0 0 0 0 0 tempdb 243 0 0 0 0 0 tempdb 284 0 0 0 0 0 tempdb 266 0 0 0 0 0 tempdb 249 0 0 0 0 0 tempdb 254 0 0 0 0 0 tempdb 268 0 0 0 0 0 tempdb 290 0 0 0 0 0 tempdb 241 0 0 0 0 0 tempdb 280 0 0 0 0 0 tempdb 262 0 0 0 0 0 tempdb 272 0 0 0 0 0 tempdb 294 0 0 0 0 0 (19 row(s) affected)
- The fact that all these values are 0 means that all these transactions have been committed but the log has not had a checkpoint to shrink it.
- For SQL Server 2000, you will need to change sys.dm_tran_database_transactions to dm_tran_database_transactions