SQL Server Error 9002: When the Transaction Log Fills and Everything Stops

The alert came in at 04:50 on a Saturday: every write against the order database was failing with error 9002, the transaction log for the database is full. The on-call’s first instinct was the industry standard panic move — shrink the log — which did nothing, because a log that cannot be truncated cannot be shrunk. By the time I was on the bridge the log file had grown to 512 GB against a 180 GB database, and the disk had ninety minutes of headroom left. The root cause turned out to be embarrassingly ordinary: an ETL package had opened an explicit transaction at 22:40 the night before, hit an error path nobody had tested, and gone to sleep holding the transaction open. Six hours of full-recovery log generation piled up behind that one open transaction, and nothing could be reused until it moved. Rolling back the orphaned session released the log in seconds. The disk, the monitoring, and the process around it took the rest of the weekend.

Error 9002 is one of those SQL Server failures where the error message tells you the symptom and nothing else, and the path to the cause runs through one DMV column most people have never read closely. This is the diagnostic sequence I use, in the order I use it, the fixes that reliably make things worse, and the sizing and alerting that keep it from recurring. Applies to SQL Server 2016 through 2022 and Azure SQL Managed Instance.

What is the log actually waiting on?

SQL Server cannot reuse a single byte of the transaction log until the reason it is being held goes away, and the engine tells you that reason directly in sys.databases — the log_reuse_wait_desc column, which is the first and usually the last place you need to look. The moment 9002 hits, run this before touching anything:

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE database_id = DB_ID(N'Orders');

-- how full is the log right now
DBCC SQLPERF(LOGSPACE);

-- is an open transaction the blocker
DBCC OPENTRAN(N'Orders');

The value in log_reuse_wait_desc is the entire diagnosis in one word. ACTIVE_TRANSACTION means an open transaction is holding the oldest log record — that was our Saturday. LOG_BACKUP means you are in full or bulk-logged recovery and nobody has taken a log backup recently enough, which is the most common cause in estates where someone switched a database to full recovery without checking that log backups were actually scheduled. REPLICATION, AVAILABILITY_REPLICA, and LOG_SCAN mean a downstream consumer — the log reader agent, an uncommitted secondary, CDC — has not processed the log yet, and the log is pinned until it catches up. In our log shipping postmortems, covered in the log shipping gotchas notes, a disabled copy job held a primary’s log hostage for an entire weekend through exactly this mechanism. The wrong move is treating all of these the same, because the fix for each is different and some fixes for one make another unrecoverable.

How do I find and kill the transaction holding the log?

When log_reuse_wait_desc says ACTIVE_TRANSACTION, DBCC OPENTRAN gives you the oldest open transaction’s session, and from there the standard blocking DMVs tell you who it is, what it ran, and how long it has been asleep. The query I keep in the runbook joins the transaction DMVs to sessions and requests:

SELECT s.session_id, s.host_name, s.program_name, s.login_name,
       t.transaction_id, tat.transaction_begin_time,
       DATEDIFF(MINUTE, tat.transaction_begin_time, SYSDATETIME()) AS open_minutes,
       r.status, SUBSTRING(st.text, 1, 300) AS last_sql
FROM sys.dm_tran_active_transactions AS tat
JOIN sys.dm_tran_session_transactions AS t
  ON t.transaction_id = tat.transaction_id
JOIN sys.dm_exec_sessions AS s
  ON s.session_id = t.session_id
LEFT JOIN sys.dm_exec_requests AS r
  ON r.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
ORDER BY tat.transaction_begin_time;

What you are looking for is the pattern our ETL session showed: a transaction whose begin time is hours old, a session status of sleeping, and no active request — an application that opened a transaction and then crashed, errored, or simply forgot to commit. Killing that session rolls the transaction back and the log becomes reusable immediately. The honest caution is that rollback is not instant for a transaction that did real work: a session that updated forty million rows before falling asleep will take roughly as long to roll back as it took to do the work, and the log cannot be reused until rollback completes. During that window you are watching DBCC SQLPERF(LOGSPACE) and the disk, not making coffee.

Which emergency fixes make it worse?

Three of them, and I have watched all three deployed in a single incident. The first is shrinking the log while it is still pinned — it fails or frees nothing, because you cannot shrink below the active portion, and then it gets retried after the pin clears, which carves the file into tiny physical chunks and leaves you with the VLF fragmentation problem I covered in the transaction log VLF notes. Shrink is a one-time corrective after you have fixed the cause, done deliberately in chunks, never a reflex. The second is flipping the database to SIMPLE recovery to force truncation, which works immediately and silently breaks your point-in-time restore chain — you have traded an outage for an unrecoverable gap, and if a log shipping or backup chain depended on that log, you now owe a full backup and a re-initialization before you are protected again. The third is adding a second log file on another drive as “more space,” which does work as an emergency pressure valve, but log files do not stripe and the extra file is dead weight you will be explaining in audits for years. The correct emergency sequence is boring: read log_reuse_wait_desc, clear the specific blocker, let a log backup truncate naturally, then resize once, deliberately.

How do I size and alert so 9002 never pages me again?

The log must be sized for the largest legitimate transaction the database will ever run — the index rebuild, the archive purge, the schema migration — plus the log generated between backups during that operation, and then alerted on usage, not just on disk free space. After the Saturday incident we found the largest honest transaction was a quarterly purge that generated 90 GB of log, log backups ran every fifteen minutes, and the log file had been sized at 40 GB by whoever built the server four years earlier. The fix was a 130 GB pre-grown log, an alert at 70 percent log usage that pages before the file is full rather than after, and a guardrail alert on any transaction open longer than thirty minutes, because every 9002 I have ever responded to was visible in that query long before it was visible in the error log. A transaction aged past your business’s longest legitimate batch is not a maybe — it is the incident, arriving early enough to be cheap.

Watching log health with MonPG when SQL Server support lands

For MonPG product capabilities and setup information, see the SQL Server monitoring page. Use the diagnostics in this article to identify the measurements and operational checks your deployment needs.

Related documentation

• SQL Server: transaction log