Press "Enter" to skip to content

Finding the ACTIVE_TRANSACTION Culprits

Thamires Lemes digs into high transaction log utilization:

The transaction log in SQL Server records all changes made to a database, allowing for data recovery and consistency. When a transaction is initiated, it acquires space in the transaction log to record its activities. Long running transactions have the potential to hold the transaction log, and, depending on database write activity, cause errors and disruptions in the SQL Server environment.

It is important to point out that the transaction that is holding the transaction log might not be performing any write activities to consume additional log space, but subsequent transactions that writes to the transaction log will cause its utilization to increase, even if they are fast. The log space won’t be released until the oldest transaction concludes its execution.

Click through for a few queries on the topic. I’d also highly recommend sp_whoisactive for this kind of work.