How to find sql transactions with large logs

There are quite a few solutions on the internet dealing with runaway transaction log files.During some sql server performance troubleshooting yesterday, I was reminded of how important analysing large transactions and the impact on transaction logs.

The sys.dm_tran_database_transactions DMV returns transaction information at the database level. Creating a JOIN with the sys.dm_exec_requests DMV  (which returns details of sql requests  executing) , creates a resultset with granular information on transaction log usage per request.

What’s the point of this information? What sort of problems can be solved using this information? Here are some examples :

1)Identifying very large deletes generating large transaction log bloating

2) Find inefficient queries that consume larger than expected transaction log space. Find them and optimize them

3)Find queries which are forcing transaction log auto growth. To many auto growths can cause lots of virtual log files . Although you may need to focus on increasing the auto growth size.Read more : Monitor SQL Transaction Log AutoGrowth for performance issues

When using database_transaction_log_bytes_used be aware it’s representing the number of bytes used up to this point in the transaction. The DMV doesn’t store historical information. It may be useful to create a SQL Server job which executes this code every short while and store the output into a database table.You can then analyse the queries .

This query returns queries with transaction log usage greater than 100 MB. It's a common query found on the internet. Customise according to your requirements

set nocount on 
 go 
 declare @datetime datetime 
 select @datetime = GETDATE() 
 select @datetime logtime, text, tr.database_id, tr.transaction_id, database_transaction_log_bytes_used, database_transaction_log_bytes_reserved, 
database_transaction_log_record_count, database_transaction_state, database_transaction_status, 
database_transaction_log_bytes_used_system, database_transaction_log_bytes_reserved_system 
 from sys.dm_tran_database_transactions  tr 
 inner join sys.dm_exec_requests r 
 on tr.transaction_id = r.transaction_id 
 cross apply sys.dm_exec_sql_text(sql_handle) 
 where database_transaction_log_bytes_used >  100*1024*1024  -- 100 MB

Read More on Transaction Logs

Modify sql transaction log file size - increase or decrease - SQL ...

Manage Transaction Logs during Index Rebuild - SQL Server DBA

SQL LOG FILE AUTOGROW performance troubleshooting