SQL Server Transaction Log Filling Up: ACTIVE_TRANSACTION and ONLINE Index Rebuild

Correction, 6 September 2026. Three passages were corrected after a technical review: the implication that sleeping sessions hold no log was removed (the query already lists only sessions with an open transaction), percent_complete is no longer presented as a progress meter for ALTER INDEX REBUILD (it stays 0 there), and the impression that a log backup during rollback frees the log was corrected. The case narrative and the measurements did not change.

Hello everyone,

I recently ran into a classic alert on a production system, the kind that catches everyone sooner or later. In this short post I will walk through that case from start to finish with the names anonymised: how we found who was holding the log, what we based the decision to kill or wait on, and most importantly what to do so it does not happen again.

First thing in the morning, this alert lands on the monitoring screen:

Transaction Log Capacity
database_name   log_size_MB   space_used_%   threshold_%   reason
APP_DB          300000        83             80            ACTIVE_TRANSACTION

The log file has climbed towards 300 GB, it is 83% full and still growing. In that situation most of us reach for the same reflex: "log backup is running but no space is being freed, let me take another backup right now." You take it and nothing changes. Because the real message is hiding in the reason column: ACTIVE_TRANSACTION. Let us take it step by step.

Why is the log backup not freeing space?

In the FULL recovery model, two conditions have to hold before the transaction log can truncate, meaning before the completed part inside it becomes reusable:

1. A log backup has been taken,
2. and no active transaction is still holding that region.

The log file is made of virtual log files (VLF) and truncation can only move forward as far as the oldest active transaction. If a single transaction has been open for hours, you cannot truncate past that point no matter how many backups you take of what comes after it. Your backup chain can be flawless and the log will still keep growing.

SQL Server tells you this in a single column:

SELECT name, log_reuse_wait_desc
FROM sys.databases
WHERE name = 'APP_DB';

The log_reuse_wait_desc value tells you why the log cannot be reused. The common ones:

In our scenario the value is ACTIVE_TRANSACTION. So the problem is not in the backup, it is in a transaction that stayed open. We need to find it.

Finding the culprit

First stop, the classic one, and still the fastest:

DBCC OPENTRAN('APP_DB');

This command gives you the SPID and start time of the oldest active transaction in the database. What holds the log is almost always that transaction.

Knowing the SPID is not enough though. Before deciding to kill, we want to see what that transaction is doing. For that I use the query below, which joins active transactions with session and request information:

SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status,
    r.command,
    r.status                                              AS request_status,
    r.wait_type,
    r.blocking_session_id,
    t.transaction_begin_time,
    DATEDIFF(MINUTE, t.transaction_begin_time, GETDATE()) AS open_minutes,
    st.text                                               AS sql_text
FROM sys.dm_tran_active_transactions t
JOIN sys.dm_tran_session_transactions se ON se.transaction_id = t.transaction_id
JOIN sys.dm_exec_sessions s              ON s.session_id = se.session_id
LEFT JOIN sys.dm_exec_requests r         ON r.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) st
ORDER BY t.transaction_begin_time ASC;

The result can hold dozens of rows. But because we order by transaction_begin_time ASC, the top row is the oldest, which makes it our prime suspect. In our case the table looked like this (abbreviated):

Learning to read this table is the critical part. Most of the rows are noise:

• The ones on msdb / master (503, 138) have nothing to do with our database.
• 606 looks sleeping and its sql_text is empty, but its transaction is only one minute old. It cannot explain three hours of growth. It is ruled out by age, not by being asleep.
• The fresh work with open_minutes = 0 (131, 527) has only just started and cannot be the cause of three hours of log growth.

The distinction matters. Seeing a sleeping row here does not mean that session holds no log. The query starts from sys.dm_tran_active_transactions and joins through sys.dm_tran_session_transactions, so by construction every row already has an open transaction. An idle pooled connection never shows up in this result at all.

An empty sql_text does not mean there is no transaction either. That column comes from sys.dm_exec_requests and stays empty when nothing is executing right now. If an application ran BEGIN TRAN, finished its work, never sent COMMIT and left the connection sitting there, the table looks exactly like this: nothing is running and the log stays put. A sleeping session with a long open transaction is not noise to rule out. It is the cause people miss most often. Look at the age, not the status.

That leaves a single row:

SPID 448 - an ONLINE index rebuild on APP_DB, open for 200 minutes, launched by a SQL Agent maintenance job:

ALTER INDEX [IX_LargeTable_1] ON [dbo].[LargeTable]
REBUILD PARTITION = N
WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON, MAXDOP = 32, RESUMABLE = OFF);

There it is, the thing holding the log. A maintenance job rebuilding a single partition of a huge partitioned table in ONLINE mode. And this is exactly where the most instructive part begins.

Why is an ONLINE index rebuild a "log monster"?

An ONLINE index rebuild does what its name says and runs without locking the table, so the application can keep reading and writing. That comes at a price:

• The operation runs inside a single, long lived transaction. The bigger the partition, the longer that transaction stays open.
• For that whole time the log cannot truncate, because this is the oldest active transaction.
• High parallelism such as MAXDOP = 32 on a large partition means a job that can run for hours and generate hundreds of GB of log.

So as long as the rebuild continues, the log file moves in one direction: up. Even if your backup chain is working perfectly.

The moment of decision: KILL or wait?

The alarm is ringing, the log is at 83% and climbing. Instinct says "kill it now". But hold on, because this is an ONLINE rebuild and note the setting: RESUMABLE = OFF.

What happens when you say KILL 448?

1. The entire partition rebuild is rolled back. Because RESUMABLE = OFF it cannot pick up where it left off. The work done is thrown away and it has to start from scratch.
2. The rollback generates log of its own and usually takes time in proportion to how far the work had progressed. Kill a job that is halfway through and the rollback can take a good while.
3. The critical one: the ACTIVE_TRANSACTION state continues until the rollback finishes. The log does not relax the moment you kill; it can even swell a little more during the rollback. The log only clears once the rollback completes and the transaction genuinely closes.

So the expectation that "kill equals instant relief" is wrong. The decision comes down to one question:

Is there room to grow on the disk or in the log file?

Scenario A, there is room (preferred): Do not touch it, wait for it to finish. Once the rebuild commits, your normal log backup chain truncates the log and the fill level drops. You avoid the cost of a pointless rollback and of running the whole job again. This is the cleanest outcome.

While you wait, all you need to do is watch the work:

SELECT
    r.status,
    r.wait_type,
    r.blocking_session_id,
    DATEDIFF(MINUTE, r.start_time, GETDATE()) AS running_min,
    r.cpu_time,
    r.logical_reads,
    r.writes,
    dt.database_transaction_log_bytes_used / 1048576.0 AS log_mb
FROM sys.dm_exec_requests r
LEFT JOIN sys.dm_tran_session_transactions st ON st.session_id = r.session_id
LEFT JOIN sys.dm_tran_database_transactions dt
       ON dt.transaction_id = st.transaction_id
      AND dt.database_id    = DB_ID('APP_DB')
WHERE r.session_id = 448;

There is a trap here. percent_complete in sys.dm_exec_requests is populated only for the specific commands Microsoft documents. That list includes ALTER INDEX REORGANIZE. A plain ALTER INDEX REBUILD is not on it, so during a rebuild the column reads 0, and that does not mean the work has stalled. estimated_completion_time is marked internal use in the documentation and should not be presented as a remaining time estimate. Reading those two columns, panicking and typing KILL is the most common mistake around this alarm.

You tell that the work is moving from counters that grow. If cpu_time, logical_reads and writes are rising, the engine is busy. If blocking_session_id is empty, nothing is holding it. Run the query twice a few minutes apart and compare. A single snapshot tells you nothing.

Keep taking your normal log backups meanwhile. They still truncate the parts that do not belong to the open transaction and slow the growth.

Scenario B, space is running out or the disk is about to fill: The reflex here is "then kill it", and it usually makes things worse. KILL does not finish the work, it undoes it. Undoing generates log too, and on a large partition rebuild it can take longer than the original operation. You would be adding a growing rollback to a log that is already about to fill.

Try to buy space first. Check whether autogrowth is on and capped, whether the volume has free space, and whether you can add a second log file on another volume. All three take minutes and none of them throws away completed work. You can remove the second log file once the rebuild finishes.

If buying space is genuinely impossible, KILL is the last resort:

KILL 448;

Track the rollback progress:

KILL 448 WITH STATUSONLY;

Keep taking log backups during the rollback, but know what they do: this does not release the log the rollback is holding. The transaction is still active while it rolls back, log_reuse_wait_desc keeps saying ACTIVE_TRANSACTION, and that portion cannot be truncated. The backup only takes what is genuinely inactive and keeps your chain going. The sentence from the top of this post still holds: while a transaction is open, the log moves one way.

BACKUP LOG APP_DB TO DISK = 'X:\Backup\APP_DB_log_rollback.trn';

Check the fill level at every step:

DBCC SQLPERF(LOGSPACE);

In our case there was room on the disk, so the right move was to wait. (The job was stopped on an operational decision and once the rollback completed the log backup chain recovered the space. The "wait while there is room" principle still stands.)

Confirming after a kill

Whatever you do, verify the state before you close the incident:

-- Log artık serbest mi?
SELECT log_reuse_wait_desc FROM sys.databases WHERE name = 'APP_DB';
-- Artık 'ACTIVE_TRANSACTION' görmemeli; 'NOTHING' veya 'LOG_BACKUP' olmalı

-- Doluluk düştü mü?
DBCC SQLPERF(LOGSPACE);

If log_reuse_wait_desc still shows ACTIVE_TRANSACTION, the rollback is still running. Wait it out, and do not go killing a second session.

The real fix: never landing here again

Putting out the fire is fine, but the same fire breaking out every week is not something a good DBA accepts. The root cause in this case was not a one off accident, it was the design of a maintenance job. That same large partition was going to swell the log the same way in the next maintenance window.

1. Use RESUMABLE = ON

Resumable online index rebuild, which arrived with SQL Server 2017, changes the rules of the game:

ALTER INDEX [IX_LargeTable_1] ON [dbo].[LargeTable]
REBUILD PARTITION = N
WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 30 MINUTES);

What you get:

• You can pause the work with PAUSE. On pause the transaction closes and the log can truncate. Take a log backup, free the space, then carry on where you left off with RESUME.
• MAX_DURATION breaks the work into automatic chunks, and the log gets to breathe between them.
• Even if you do have to kill it, you do not start over. It resumes from where it stopped.

In other words, instead of holding the log inside one giant transaction, you cut it into controlled small slices.

2. Revisit the maintenance strategy

• Rather than REBUILD every time, split REORGANIZE and REBUILD by a fragmentation threshold (Ola Hallengren's maintenance solution offers this out of the box). REORGANIZE moves forward in smaller transactions.
• Move partition level rebuilds to an off-peak window where log backups run frequently.
• On huge tables, rebuild the partitions one at a time or in rotation rather than all at once.

3. Proactive monitoring

Watch the log_reuse_wait_desc value periodically. Turn a long stuck ACTIVE_TRANSACTION into an alert, so you hear about it at 50% rather than at 83%.

To sum up

If you meet the same alert one day, the order goes:

1. Confirm the diagnosis: is sys.databases.log_reuse_wait_desc showing ACTIVE_TRANSACTION?
2. Find the culprit: DBCC OPENTRAN plus the active transaction DMV query. Look for the oldest, longest open transaction.
3. Understand what it is: an ONLINE index rebuild, an orphaned application transaction, a long ETL? The decision changes accordingly.
4. Get the kill decision right:
   • If there is room on disk, wait and do not pay the rollback cost.
   • If space is running out, kill and take log backups in a loop.
5. Verify: is log_reuse_wait_desc now NOTHING/LOG_BACKUP and has DBCC SQLPERF(LOGSPACE) dropped?
6. Fix it at the root: RESUMABLE = ON on large tables, split maintenance windows and proactive monitoring.

Half the solution is simply that your first move on seeing ACTIVE_TRANSACTION is not "let me take another backup". The other half is never pressing kill before you understand what that open transaction is. Waiting patiently while there is room on disk is usually the more mature answer.

Questions welcome, and good luck out there.

Frequently asked questions

Why does a log backup not free space while the transaction log fills because of ACTIVE_TRANSACTION?

In the FULL recovery model the log can only truncate as far as the oldest active transaction. If a transaction has been open for hours, no amount of backing up what comes after it will free space beyond that point. When log_reuse_wait_desc = ACTIVE_TRANSACTION, the problem is not the backup, it is the open transaction.How do you find the active transaction holding the log?

DBCC OPENTRAN gives you the SPID of the oldest active transaction. For detail, order the sys.dm_tran_active_transactions, sys.dm_tran_session_transactions, sys.dm_exec_sessions/requests and sys.dm_exec_sql_text DMVs ascending by transaction_begin_time. The top row is the oldest, which makes it the prime suspect.Should you kill a long running ONLINE index rebuild or wait?

If there is room on the disk and in the log file, waiting is cleanest: once the rebuild commits the log backup chain truncates. If space is running out, kill it and take a log backup every two or three minutes throughout the rollback. A kill brings no instant relief; ACTIVE_TRANSACTION continues until the rollback finishes, and the rollback generates log as well.What is the permanent fix so this does not happen again?

On large tables run the rebuild with RESUMABLE = ON and MAX_DURATION. On PAUSE the transaction closes, the log truncates, and you carry on with RESUME. Also use a REORGANIZE/REBUILD threshold, move maintenance off-peak and monitor log_reuse_wait_desc proactively.The database, server and object names in this post have been anonymised. The scenario is based on a real production case.