SQL Server Transaction Log Getting Full? Find the Session Causing It (Real Demo)
This video demonstrates a common but often misunderstood SQL Server issue where the transaction log becomes full even though log backups are running successfully. Using a live demo in FULL recovery model, we show how a large transaction that is left open can quickly consume the entire transaction log and block all new work in the database.
As the demo progresses, you observe typical production symptoms: the transaction log grows rapidly, `DBCC SQLPERF(LOGSPACE)` reports usage near 100 percent, and log backups fail to reduce log space. Eventually, new transactions begin to fail with the error stating that the transaction log is full due to an ACTIVE_TRANSACTION, impacting applications and users.
The root cause is explained by understanding the *active portion of the transaction log**, also known as the active log. The active log spans from the **MinLSN (Minimum Log Sequence Number)* to the most recently written log record. This section of the log is required for full database recovery and **can never be truncated**. Only log records that exist before the MinLSN are eligible for truncation.
Through a simplified example, the video explains how a single active transaction can determine the MinLSN. Even if other transactions have committed and checkpoints have occurred, SQL Server must retain all log records from the beginning of the oldest active transaction. As long as that transaction remains open, the active log continues to grow and log backups cannot free any space.
The video concludes with practical solutions and best practices. You learn how to identify open transactions using `DBCC OPENTRAN`, why committing or rolling back the transaction is the correct fix, and when taking a log backup will finally allow truncation. An emergency option using SIMPLE recovery is discussed with caution, along with guidance on proper transaction design, log sizing, and monitoring to prevent this issue in production.
Download Demo code from GitHub: https://github.com/TheFriendlyDBA/Ski...