SQL Server Transaction Log Full (Error 9002): A Read-First log_reuse_wait_desc, Log Backup, and Open Transaction Triage

Helpdesk: "The finance app throws 'The transaction log for database FinanceDB is full due to LOG_BACKUP' on every save. Reports still open, nothing saves. The L: drive on SQL01 is at 100%. The night shift says just switch the database to SIMPLE and shrink the log. Can you do that?"

Don't flip the recovery model yet. "Transaction log full" (SQL Server error 9002) is rarely a mystery, and it's rarely fixed by shrinking. SQL Server tells you why the log can't be reused, and the fix depends on that reason. It's one layer in a documented chain: the exact error text names the database and the reason (due to 'LOG_BACKUP', 'ACTIVE_TRANSACTION', 'AVAILABILITY_REPLICA', ...) → log_reuse_wait_desc in sys.databases confirms what is holding the log right now → the log file is full because it can't truncate, because the disk volume is full, or because it hit a MAXSIZE cap or has autogrow off → the recovery model and the msdb backup history show whether log backups are actually running → an open or long-running transaction is pinning the start of the log → an availability group secondary, replication, CDC, or mirroring hasn't consumed the log yet → a routine reason (checkpoint, log scan, snapshot creation) clears by itself → the SQL Server error log shows when 9002 started and what ran just before it. sys.databases, sys.dm_db_log_space_usage, sys.dm_db_log_stats, sys.master_files, sys.dm_os_volume_stats, msdb.dbo.backupset, DBCC OPENTRAN, the sys.dm_hadr_* views, and Get-SqlErrorLog are the sources for that chain. Switching to SIMPLE and shrinking can make the error go away tonight. It also breaks the log backup chain, and if the real reason was an open transaction or a lagging AG secondary, the log fills again by morning.

If the server itself lost its domain trust after a restore or snapshot rollback, start with Trust Relationship Between This Workstation and the Primary Domain Failed. If the real question is what you can restore when it goes wrong, read Retention Is Not Backup for the restore-drill habit. This post covers "a SQL Server database is returning error 9002 (the transaction log is full), or its log file keeps growing until the disk fills, and you need a read-first path through the reason, the file and disk, backups, open transactions, availability groups and replication, and the error log before you shrink, switch recovery models, kill sessions, or add files."

TL;DR

• Read the reason first. Microsoft's 9002 message says it directly: "To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases." LOG_BACKUP, ACTIVE_TRANSACTION, AVAILABILITY_REPLICA, and REPLICATION each have a different fix.
• Truncation is not shrinking. Microsoft: truncation is a logical operation (normally during a log backup) that frees space inside the log. Shrinking returns physical space to the disk, and "shrinking a log file alone can't solve the problem of a full log file."
• Online means read-only, not down. If the log fills while the database is online, Microsoft says it stays online but can only be read, not updated. If it fills during recovery, the database is marked RESOURCE PENDING.
• FULL and BULK_LOGGED need log backups. Microsoft: in those models the log "continues to grow until you perform a transaction log backup." If the log has never been backed up, you need two log backups before it truncates.
• Long transactions block truncation in every recovery model, including SIMPLE. DBCC OPENTRAN and sys.dm_tran_database_transactions find them.
• Disk full and MAXSIZE are separate causes. sys.master_files (size, max_size, growth) and sys.dm_os_volume_stats tell you whether the log can't truncate, can't grow, or both.

---

Where in the chain did it break?

Step	What should be true	Typical symptom when it breaks
1. Symptom captured	Exact 9002 text with database name and due to reason, online vs RESOURCE PENDING, when it started, what changed	"SQL is down" with no database, reason, or time
2. Reason read	log_reuse_wait_desc read for the affected database	Shrinking or switching models without knowing why the log is held
3. File and disk	Log can truncate, the volume has space, MAXSIZE and autogrow allow growth	Log "full" because the drive or a cap is full, not because truncation stopped
4. Backups	Recovery model is intentional; log backups run and succeed	LOG_BACKUP on a FULL database whose log backup job broke weeks ago
5. Open transactions	No session holding a transaction open for hours	ACTIVE_TRANSACTION behind an idle session or a huge batch
6. HA and replication	AG secondaries, Log Reader Agent, CDC, mirroring keep up	AVAILABILITY_REPLICA or REPLICATION while backups look fine
7. Routine reasons	CHECKPOINT, LOG_SCAN, snapshot creation clear quickly	A brief reason treated as the root cause
8. Error log	9002 timeline and preceding errors read	No idea when it started or which job ran first
9. Change	Only after Steps 1–8 agree: one layer, one change	Recovery model flipped, log shrunk, sessions killed, still full next day

---

Step 1: Capture the exact error (read-only)

Microsoft's MSSQLSERVER_9002 page lists the event as Event ID 9002, source MSSQLSERVER, symbolic name LOG_IS_FULL. The base text is "The transaction log for database '%.*ls' is full." Most real-world messages add the reason, for example ... is full due to 'LOG_BACKUP' (Microsoft's AG article shows the same pattern with 'AVAILABILITY_REPLICA'). Write these on the ticket before anyone changes anything:

Fact	Why it matters
Database name	9002 is per database. tempdb filling is a different conversation from a user database
The due to reason in the message	The fastest pointer to the right step. Confirm it in Step 2, because it can change
Online or RESOURCE PENDING?	Microsoft: online means read-only for writes. RESOURCE PENDING means it filled during recovery and needs ALTER DATABASE ... SET ONLINE after the fix
When did it start?	Lines up with a failed backup job, an index rebuild, a big import, an AG or replication outage, or a disk change
Is the log drive full?	Separates "can't truncate" from "can't grow" (Step 3)
What changed?	Backup tool replaced, maintenance plan edited, recovery model changed, new AG replica, new publication or CDC

Then match the symptom:

What you see	Likely layer
due to 'LOG_BACKUP', FULL or BULK_LOGGED database	Log backups missing or failing (Step 4)
due to 'ACTIVE_TRANSACTION'	Open or long-running transaction (Step 5)
due to 'AVAILABILITY_REPLICA'	AG secondary not hardening or redoing fast enough (Step 6)
due to 'REPLICATION'	Log Reader Agent, or CDC, behind or stopped (Step 6)
due to 'ACTIVE_BACKUP_OR_RESTORE'	A data backup or restore is still running (Step 4)
due to 'CHECKPOINT' / 'LOG_SCAN' / snapshot creation	Usually routine and brief (Step 7)
Reason looks fine, log drive at 100%	Disk volume full (Step 3)
Log file stuck at a round number, autogrow off or MAXSIZE reached	Size cap (Step 3)
tempdb log full	Long-running transactions using tempdb (Step 5)

---

Step 2: Read log_reuse_wait_desc (read-only)

Microsoft's troubleshooting article: "To discover what is preventing log truncation in a given case, use the log_reuse_wait and log_reuse_wait_desc columns of the sys.databases catalog view."

-- Read-only. Every database that can't reuse log space right now
SELECT name, database_id, recovery_model_desc, state_desc,
       log_reuse_wait, log_reuse_wait_desc
FROM sys.databases
WHERE log_reuse_wait > 0
ORDER BY name;

The same from PowerShell (SqlServer module):

# Read-only. SqlServer module; add -TrustServerCertificate if your module version and TLS setup need it
Invoke-Sqlcmd -ServerInstance 'SQL01' -Database 'master' -Query @"
SELECT name, recovery_model_desc, state_desc, log_reuse_wait_desc
FROM sys.databases
WHERE log_reuse_wait > 0;
"@ | Format-Table -AutoSize

What each value means, from Microsoft's "Factors that can delay log truncation" table:

log_reuse_wait_desc	Microsoft's description (short)	Go to
NOTHING	One or more reusable VLFs exist	Step 3 (disk or cap), if 9002 still fires
CHECKPOINT	No checkpoint since the last truncation, or the head of the log hasn't moved past a VLF. Routine	Step 7
LOG_BACKUP	A log backup is required before the log can truncate (FULL or BULK_LOGGED only)	Step 4
ACTIVE_BACKUP_OR_RESTORE	A data backup or restore is in progress	Step 4
ACTIVE_TRANSACTION	A transaction is active or deferred (all recovery models)	Step 5
DATABASE_MIRRORING	Mirroring paused, or the mirror is far behind	Step 6
REPLICATION	Transactions for publications not yet delivered to the distribution database	Step 6
DATABASE_SNAPSHOT_CREATION	A database snapshot is being created. Routine, typically brief	Step 7
LOG_SCAN	A log scan is occurring. Routine, typically brief	Step 7
AVAILABILITY_REPLICA	A secondary replica is still applying this database's log	Step 6
OLDEST_PAGE	With indirect checkpoints, the oldest page is older than the checkpoint LSN	Step 7
XTP_CHECKPOINT	An In-Memory OLTP checkpoint is needed	Step 7

Values 10, 11, and 12 are "for internal use only," and 14 (OTHER_TRANSIENT) is "currently not used." Read the value more than once over a few minutes. The reason can move (for example from LOG_BACKUP to ACTIVE_TRANSACTION after a log backup), and the second reason is often the real one.

sys.dm_db_log_stats (SQL Server 2016 SP2 and later) gives the same reason as log_truncation_holdup_reason, plus the last log backup time and VLF counts in one row:

-- Read-only. One row per database
SELECT d.name, ls.recovery_model, ls.log_truncation_holdup_reason,
       ls.total_log_size_mb, ls.active_log_size_mb,
       ls.log_backup_time, ls.log_since_last_log_backup_mb,
       ls.total_vlf_count, ls.active_vlf_count
FROM sys.databases AS d
CROSS APPLY sys.dm_db_log_stats(d.database_id) AS ls
ORDER BY ls.active_log_size_mb DESC;

Microsoft notes that on an AG secondary database this function returns only database_id, recovery_model, and log_backup_time, so run it on the primary.

---

Step 3: Can't truncate, can't grow, or both? (read-only)

Microsoft's article lists four common causes: the log isn't being truncated, the disk volume is full, the log size is capped or autogrow is disabled, and replication or AG synchronization can't complete. Steps 4–6 cover truncation. This step covers the file and the disk.

-- Read-only. Run in the affected database: how full is the log?
USE FinanceDB;
SELECT total_log_size_in_bytes / 1048576.0 AS total_log_mb,
       used_log_space_in_bytes / 1048576.0 AS used_log_mb,
       used_log_space_in_percent,
       log_space_in_bytes_since_last_backup / 1048576.0 AS since_last_log_backup_mb
FROM sys.dm_db_log_space_usage;

sys.dm_db_log_space_usage combines all log files for the current database. log_space_in_bytes_since_last_backup applies to SQL Server 2014 and later. Permissions: VIEW SERVER STATE on SQL Server 2019 and earlier, VIEW SERVER PERFORMANCE STATE on 2022 and later. DBCC SQLPERF(LOGSPACE) still returns log size and percent used for every database, but Microsoft says that starting with SQL Server 2012 you should use sys.dm_db_log_space_usage instead.

-- Read-only. Every log file: size, cap, growth, and free space on its volume
SELECT DB_NAME(mf.database_id) AS db_name, mf.name AS log_name, mf.physical_name,
       CONVERT(BIGINT, mf.size) * 8 / 1024 AS size_mb,
       CASE WHEN mf.max_size = -1 THEN NULL ELSE CONVERT(BIGINT, mf.max_size) * 8 / 1024 END AS max_size_mb,
       mf.growth, mf.is_percent_growth,
       vs.volume_mount_point, vs.available_bytes / 1048576 AS volume_free_mb
FROM sys.master_files AS mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs
WHERE mf.type_desc = 'LOG'
ORDER BY size_mb DESC;

How to read it:

Reading	What it means	Next
High used %, log_reuse_wait_desc not NOTHING	The log can't truncate	Steps 4–7 by reason
Volume free space near zero	The log can't grow even if it would truncate later	Free space or add a temporary log file (Step 9)
growth = 0	Autogrow disabled. Microsoft's own check uses growth = 0	Enable growth deliberately (Step 9)
Size close to max_size	Capped. Microsoft: increase MAXSIZE if the current limit is reached	Step 9
Percent growth (is_percent_growth = 1)	Microsoft recommends growth in size, not percent, because "a percentage is an ever-growing amount"	Fix after the incident
Log at 2 TB	Microsoft: the maximum size for a log file is 2 TB	Add a log file on another volume (temporary)

Microsoft also says: even with autogrowth on, "you can receive a message that the transaction log is full if it can't grow fast enough." A tiny growth increment under a big batch can do that. And: "Log files should never be placed on compressed file systems."

---

Step 4: Backups and the recovery model (read-only)

For LOG_BACKUP, Microsoft's first move is to check the recovery model and the backup history.

-- Read-only. Recovery model and the last two months of backups (Microsoft's sample, trimmed)
SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'FinanceDB';

SELECT bs.database_name, bs.type, bs.is_copy_only, bs.recovery_model,
       bs.backup_start_date, bs.backup_finish_date,
       bf.physical_device_name
FROM msdb.dbo.backupset AS bs
LEFT JOIN msdb.dbo.backupmediafamily AS bf ON bs.media_set_id = bf.media_set_id
WHERE bs.database_name = 'FinanceDB'
  AND bs.backup_start_date > DATEADD(month, -2, SYSDATETIME())
ORDER BY bs.backup_start_date DESC;

type = D is a full backup, I differential, L transaction log (Microsoft's sample maps the rest).

What Microsoft says that changes how you read the history:

Fact	Why it matters
FULL and BULK_LOGGED require log backups	"The transaction log continues to grow until you perform a transaction log backup"
Enterprise and Standard default to FULL; Express defaults to SIMPLE	A database created on Standard with nobody scheduling log backups fills its log by design. The model database sets the default for new databases
A new FULL database acts like SIMPLE until its first full backup	Microsoft: the log is reused as needed "up until the time you create a full database backup." That's why the problem appears right after the first full backup job starts
Truncation happens after a log backup if a checkpoint has occurred since the previous backup	Routine timing, not a fault
Copy-only log backups don't truncate	Microsoft's truncation rule excludes them. A third-party tool taking copy-only log backups leaves the log growing
Never backed up?	Microsoft: "you must create two log backups to permit the Database Engine to truncate the log"

If the history shows log backups stopped on a date, read the SQL Agent job or backup tool for that date before you take a manual one. The manual backup clears tonight's error. The broken job fills the log again next week.

ACTIVE_BACKUP_OR_RESTORE

-- Read-only. Microsoft's check for running backups or restores
SELECT session_id, command, percent_complete, start_time, estimated_completion_time
FROM sys.dm_exec_requests
WHERE command LIKE 'BACKUP%' OR command LIKE 'RESTORE%';

Microsoft: if a data backup is preventing truncation, "canceling the backup operation might help the immediate problem." Usually waiting is the better choice. A slow full backup on a big database is also why Microsoft's log sizing tip says to monitor log use during "the time required to run a full backup, because log backups can't occur until it finishes."

---

Step 5: Open transactions (read-only)

ACTIVE_TRANSACTION means something is holding the start of the log. Microsoft: long-running transactions prevent truncation "under all recovery models, including the simple recovery model." A deferred transaction (rollback blocked by an unavailable resource) counts too.

-- Read-only. Oldest active transaction in the database (sysadmin or db_owner)
DBCC OPENTRAN ('FinanceDB') WITH TABLERESULTS, NO_INFOMSGS;

-- Read-only. Who owns the open transactions, how old, how much log (Microsoft's columns + session info)
SELECT st.session_id, s.login_name, s.host_name, s.program_name, s.status,
       dt.database_transaction_begin_time,
       dt.database_transaction_state,
       dt.database_transaction_log_bytes_used / 1048576.0 AS log_used_mb,
       s.last_request_end_time
FROM sys.dm_tran_database_transactions AS dt
LEFT JOIN sys.dm_tran_session_transactions AS st ON dt.transaction_id = st.transaction_id
LEFT JOIN sys.dm_exec_sessions AS s ON st.session_id = s.session_id
WHERE dt.database_id = DB_ID('FinanceDB')
ORDER BY dt.database_transaction_begin_time;

Microsoft calls out database_transaction_begin_time (time of the first log record), database_transaction_state, and database_transaction_begin_lsn as the columns of interest for long-running transactions. DBCC OPENTRAN gives you the session ID, the start time, and the SID of the owner "so you can potentially track down the source of the transaction for a more orderly termination (committing it rather than rolling it back)."

Patterns we see most:

Pattern	What you see	Read-first check
Idle session with an open transaction	status = sleeping, old begin_time, last_request_end_time hours ago	Who is it (login_name, host_name, program_name)? Someone's SSMS window with BEGIN TRAN and no commit
Huge batch or index maintenance	Running session, log_used_mb climbing	The job schedule. Microsoft's sizing tip watches log use during "the largest index maintenance operations" and "the largest batch"
Application without commits	Same app login, many sessions, old transactions	The app owner, before you kill anything
tempdb log full	tempdb reason is ACTIVE_TRANSACTION	Microsoft: user transactions use tempdb for sorts, hashes, cursors, and row versioning "even if the user transaction includes only reading data"

Killing the session is a change (Step 9), and the rollback can take a long time.

---

Step 6: Availability groups, replication, CDC, mirroring (read-only)

If the reason is AVAILABILITY_REPLICA, Microsoft's AG article says the primary can't truncate until log blocks are hardened on every secondary and redone by every secondary's redo thread. Two scenarios: latency delivering log to a secondary, and redo latency on a secondary.

-- Read-only. On the primary: which secondary holds the earliest truncation point (Microsoft's query)
SELECT ag.name AS availability_group_name, d.name AS database_name,
       ar.replica_server_name AS replica_instance_name,
       drs.truncation_lsn, drs.log_send_queue_size, drs.redo_queue_size
FROM sys.availability_groups AS ag
INNER JOIN sys.availability_replicas AS ar ON ar.group_id = ag.group_id
INNER JOIN sys.dm_hadr_database_replica_states AS drs ON drs.replica_id = ar.replica_id
INNER JOIN sys.databases AS d ON d.database_id = drs.database_id
WHERE drs.is_local = 0
ORDER BY ag.name, d.name, drs.truncation_lsn, ar.replica_server_name;

Reading	Microsoft's meaning	What to look at
High log_send_queue_size	Log records that haven't arrived at the secondary	Network latency or throughput, a disconnected or suspended replica
High redo_queue_size	Log hardened on the secondary but not yet redone (KB)	Resources on the secondary; a redo thread blocked by readers (lock_redo_blocked extended event)
Lowest truncation_lsn	The secondary delaying truncation the most	That replica first

Microsoft notes this happens "whether the secondary replica is set for synchronous or asynchronous commit mode," so an async DR replica that's been offline for a day can fill the primary's log.

For REPLICATION (and change tracking or CDC), Microsoft's article says these features rely on the log, and points you to DBCC OPENTRAN (it also shows the oldest distributed and non-distributed replicated transactions), Replication Monitor, and the Log Reader Agent's status and errors. For DATABASE_MIRRORING, Microsoft's script reads sys.database_mirroring for the state and role and uses the mirroring monitor procedures for unsent and unrestored log.

---

Step 7: Checkpoint, VLFs, and the routine reasons (read-only)

Microsoft calls CHECKPOINT, LOG_SCAN, and DATABASE_SNAPSHOT_CREATION routine and typically brief. If CHECKPOINT persists, Microsoft suggests running a checkpoint (a change, Step 9) and examining the VLFs:

-- Read-only. VLF layout (SQL Server 2016 SP2 and later): which VLFs are active and where they sit
SELECT file_id, vlf_sequence_number, vlf_size_mb, vlf_active, vlf_status
FROM sys.dm_db_log_info(DB_ID('FinanceDB'))
ORDER BY vlf_sequence_number;

Why this matters: Microsoft says truncation "occurs on a virtual-log-file (VLF) boundary," and a shrink can only remove inactive VLFs from the end of the file. An active VLF near the end of the file is why a shrink right after a successful log backup frees almost nothing. Microsoft also links large VLF counts to slower startup, restore, and recovery (its example flags more than 100).

OLDEST_PAGE (indirect checkpoints) and XTP_CHECKPOINT (In-Memory OLTP; Microsoft says an automatic checkpoint happens when the log grows past 1.5 GB since the last one) are rarer. Microsoft's script suggests that if OLDEST_PAGE lasts a long time you can consider temporarily disabling indirect checkpoint (TARGET_RECOVERY_TIME = 0 SECONDS). Treat that as a deliberate change, not a first move.

---

Step 8: Confirm the timeline in the error log (read-only)

# Read-only. SqlServer module: SQL Server error log since yesterday, 9002 and neighbors
Get-SqlErrorLog -ServerInstance 'SQL01' -Since Yesterday |
  Where-Object { $_.Text -match '9002|transaction log|is full' } |
  Format-Table Date, Source, Text -Wrap

# Read-only. Windows Application log on the SQL Server host (Microsoft lists event ID 9002, source MSSQLSERVER)
Get-WinEvent -FilterHashtable @{ LogName = 'Application'; Id = 9002 } -MaxEvents 20 -ErrorAction SilentlyContinue |
  Format-Table TimeCreated, ProviderName, Message -Wrap

Filtering on the ID rather than the provider keeps this working on named instances, whose event source is usually MSSQL$<instance> rather than MSSQLSERVER (our reading). Line up the first 9002 with the backup job history (Step 4), the job or batch that was running (Step 5), and any AG or replication errors (Step 6).

---

Common false starts (read these before you change anything)

False start	Why it hurts	Read-first alternative
Switching to SIMPLE, shrinking, switching back	Breaks point-in-time recovery from that moment. Microsoft: back up the log before switching from FULL, and after switching back, take a full or differential backup because "the switch to the full or bulk-logged recovery model takes effect only after the first data backup." Doesn't help at all if the reason is ACTIVE_TRANSACTION or AVAILABILITY_REPLICA	Read the reason (Step 2); for LOG_BACKUP, take a log backup
Shrinking the log first	Microsoft: a shrink only helps "when a transaction log file contains unused space," and shrink "alone can't solve the problem of a full log file"	Truncate first (fix the reason), then shrink once if the file is oversized
Deleting or moving the .ldf	Microsoft's warning on the transaction log page: "Never delete or move this log unless you fully understand the ramifications of doing so"	Free space elsewhere on the volume or add a temporary log file
Killing the session without asking	Rollback can take a long time, and the work is lost. Microsoft: use KILL "with extreme caution"	Find the owner via DBCC OPENTRAN and the session details (Step 5)
Restarting SQL Server	Doesn't truncate the log; recovery still has to process the open work, and a log that fills during recovery leaves the database RESOURCE PENDING (our reading of Microsoft's recovery note)	Fix the reason while the database is online
A second log file "forever"	Microsoft: multiple log files are "a temporary condition to resolve a space issue." They don't improve performance	Add one only to get through the incident, then remove it once space is reusable
Log backup to NUL	It truncates the log but produces a backup you can't restore, so the log chain is broken in practice (our reading)	A real log backup to real storage
Turning on AUTO_SHRINK	Microsoft doesn't recommend it; it's off by default	Right-size the log once and leave it

---

Step 9: Change one layer, then watch (only after the read)

Pick the one change Steps 1–8 point at. T-SQL has no -WhatIf, so the examples below either use a cmdlet that supports it (Backup-SqlDatabase) or a small wrapper of ours that gives any T-SQL change -WhatIf and -Confirm.

# Change helper (ours). Prints the statement under -WhatIf; runs it only without -WhatIf
function Invoke-SqlChange {
  [CmdletBinding(SupportsShouldProcess, ConfirmImpact = 'High')]
  param(
    [Parameter(Mandatory)] [string] $ServerInstance,
    [string] $Database = 'master',
    [Parameter(Mandatory)] [string] $Query
  )
  if ($PSCmdlet.ShouldProcess("$ServerInstance/$Database", $Query)) {
    Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Database -Query $Query
  }
}

# Change (elevated, sysadmin on SQL01). Pick ONE that matches your finding

# LOG_BACKUP: take a real log backup (twice if the log has never been backed up)
Backup-SqlDatabase -ServerInstance 'SQL01' -Database 'FinanceDB' -BackupAction Log `
  -BackupFile 'M:\Backup\FinanceDB_log_20261017_0930.trn' -WhatIf
# Same backup, output as T-SQL for review instead of running it
Backup-SqlDatabase -ServerInstance 'SQL01' -Database 'FinanceDB' -BackupAction Log `
  -BackupFile 'M:\Backup\FinanceDB_log_20261017_0930.trn' -Script

# Size cap reached: raise MAXSIZE (only if the volume has room)
Invoke-SqlChange -ServerInstance 'SQL01' -Query "ALTER DATABASE [FinanceDB] MODIFY FILE (NAME = N'FinanceDB_log', MAXSIZE = 200GB);" -WhatIf

# Autogrow disabled: set a fixed growth increment (Microsoft's script uses 64 MB)
Invoke-SqlChange -ServerInstance 'SQL01' -Query "ALTER DATABASE [FinanceDB] MODIFY FILE (NAME = N'FinanceDB_log', FILEGROWTH = 64MB);" -WhatIf

# Log volume full and nothing else to free: TEMPORARY second log file on another volume
Invoke-SqlChange -ServerInstance 'SQL01' -Query "ALTER DATABASE [FinanceDB] ADD LOG FILE (NAME = N'FinanceDB_log_temp', FILENAME = N'T:\SQLLogTemp\FinanceDB_log_temp.ldf', SIZE = 8GB, FILEGROWTH = 64MB);" -WhatIf

# ACTIVE_TRANSACTION: end the session only after the owner agrees (rollback can be long)
Invoke-SqlChange -ServerInstance 'SQL01' -Query 'KILL 87;' -WhatIf

# CHECKPOINT reason that persists
Invoke-SqlChange -ServerInstance 'SQL01' -Database 'FinanceDB' -Query 'CHECKPOINT;' -WhatIf

# Database marked RESOURCE PENDING (filled during recovery), after the space problem is fixed
Invoke-SqlChange -ServerInstance 'SQL01' -Query 'ALTER DATABASE [FinanceDB] SET ONLINE;' -WhatIf

Read-only confirm afterwards (repeat until the reason settles):

SELECT name, log_reuse_wait_desc FROM sys.databases WHERE name = 'FinanceDB';
USE FinanceDB;
SELECT used_log_space_in_percent FROM sys.dm_db_log_space_usage;
-- If you ran KILL: progress of the rollback without killing anything new
KILL 87 WITH STATUSONLY;

KILL ... WITH STATUSONLY only reports rollback progress. Microsoft recommends it over repeating KILL, because a repeated KILL could end a new session that reused the ID.

Once the log is reusable, a one-time right-size shrink is reasonable if the file grew far past what the workload needs:

# Change, once, after truncation. Check the DBCC SHRINKFILE page for how target_size is calculated for log files
Invoke-SqlChange -ServerInstance 'SQL01' -Database 'FinanceDB' -Query "DBCC SHRINKFILE (N'FinanceDB_log', 16384);" -WhatIf

Microsoft's shrink guidance: shrink "shouldn't be considered a regular maintenance operation," and if the log has to grow back, "the transaction log will grow again, introducing performance overhead." Size it for your largest full backup window, index maintenance, and batch.

Changes outside the database:

• Backup job or tool: fix the schedule, the target path, or the copy-only setting so regular log backups run. That's the real LOG_BACKUP fix.
• AG secondary: fix the network, resources, or blocked redo on the lagging replica. Microsoft's temporary workarounds (removing the database from the AG on that secondary, or setting the readable secondary to ALLOW_CONNECTIONS = NO) cost you HA or read access, so plan them with the DBA owner.
• Replication / CDC: restart or repair the Log Reader Agent or capture job per its error.

Watch one full cycle: the next scheduled log backup succeeds, log_reuse_wait_desc returns to NOTHING or LOG_BACKUP between backups, used_log_space_in_percent drops after each backup, and no new 9002 lines appear in Get-SqlErrorLog.

---

When a change is justified

Finding	Prefer	Avoid
LOG_BACKUP, log backups stopped on a date	Manual log backup now, then fix the job	Switching to SIMPLE to make it stop
LOG_BACKUP on a database that doesn't need point-in-time restore	Agree with the owner, back up the log, switch to SIMPLE deliberately, stop the log backup job	Leaving it in FULL with no log backups
Log never backed up	Two log backups (Microsoft), then a schedule	One backup and a shrink, then surprise
ACTIVE_TRANSACTION, idle session	Ask the owner to commit or close; KILL only with agreement	Killing every sleeping session
ACTIVE_TRANSACTION, big batch	Let it finish if space allows; split future batches	Killing it at 95% and paying for the rollback
AVAILABILITY_REPLICA	Fix the lagging replica; temporary growth if needed	Shrinking the primary's log repeatedly
Disk volume full	Free space or a temporary log file on another volume	Deleting the .ldf, or putting logs on compressed volumes
MAXSIZE reached or autogrow off	Raise the cap or set a fixed MB growth deliberately	Unlimited percent growth on a small volume
Oversized log after the incident	One shrink to a sized target, then leave it	AUTO_SHRINK or a nightly shrink job

---

FAQ

What does "The transaction log for database is full due to LOG_BACKUP" mean?
The database is in the FULL or BULK_LOGGED recovery model and needs a transaction log backup before the log can be truncated. Check msdb.dbo.backupset for when the last log backup ran, take one, and fix whatever stopped the scheduled backups. If the log has never been backed up, Microsoft says you need two log backups before it truncates.

Why is my transaction log full if the database is in SIMPLE recovery?
SIMPLE truncates on checkpoint, but Microsoft says long-running transactions block truncation in every recovery model, including SIMPLE. Read log_reuse_wait_desc. ACTIVE_TRANSACTION points at an open transaction (DBCC OPENTRAN). If the reason is NOTHING, check the disk and the file's MAXSIZE and growth settings.

Why won't my SQL Server log file shrink?
A shrink can only remove inactive VLFs from the end of the file. If the log can't truncate (look at log_reuse_wait_desc), or an active VLF sits near the end of the file, shrink frees little or nothing. Microsoft's SHRINKFILE page says the most common reason is missing log backups: back up the log, then shrink again.

Is it safe to switch to SIMPLE recovery to fix a full log?
It clears a LOG_BACKUP problem but breaks point-in-time recovery, and it does nothing for ACTIVE_TRANSACTION, AVAILABILITY_REPLICA, or a full disk. Databases in an availability group can't use SIMPLE at all. If you do it on purpose, Microsoft says to back up the log before switching from FULL, and to take a full or differential backup right after switching back.

What does AVAILABILITY_REPLICA mean in log_reuse_wait_desc?
The primary replica can't truncate its log until every secondary has hardened and redone the log records. Compare truncation_lsn, log_send_queue_size, and redo_queue_size in sys.dm_hadr_database_replica_states to find the secondary that's holding it, then fix that replica's network, resources, or blocked redo.

---

Related posts

• Trust Relationship Between This Workstation and the Primary Domain Failed (when the SQL host itself loses its domain trust after a restore or snapshot rollback)
• Retention Is Not Backup (the same restore-drill habit, for Microsoft 365 data)
• Active Directory Account Keeps Getting Locked Out (another read-first triage, for when an expired service account password is what started the failed jobs)

Free checklist

When a line-of-business database goes read-only, the ticket storm that follows is often the afternoon someone asks to loosen or rush Conditional Access for the app's users or service accounts "just until it's back." Before you change enforcement, the free Conditional Access pre-enforcement checklist keeps break-glass accounts, pilot groups, and report-only review on one page:

👉 https://cashflow4375.gumroad.com/l/ujkqxb?utm_source=devto&utm_medium=article&utm_campaign=aps_first_sale&utm_content=sql-transaction-log-full

If you want the rest of the Intune runway written down for the team (enrollment hygiene, compliance phasing, break/fix cards and read-only PowerShell snapshots), the Intune & M365 Admin Starter Pack pairs with this triage: https://cashflow4375.gumroad.com/l/joonf?utm_source=devto&utm_medium=article&utm_campaign=aps_first_sale&utm_content=sql-transaction-log-full

---

Admin Pack Studio; not affiliated with Microsoft. Steps 1–8 favor read-only checks: sys.databases (log_reuse_wait_desc), sys.dm_db_log_stats, sys.dm_db_log_space_usage, sys.master_files, sys.dm_os_volume_stats, msdb.dbo.backupset, sys.dm_exec_requests, DBCC OPENTRAN, sys.dm_tran_database_transactions, sys.dm_exec_sessions, sys.dm_hadr_database_replica_states, sys.dm_db_log_info, Get-SqlErrorLog, and the Application event log. Backing up the log, changing recovery models, MAXSIZE or growth, adding or removing log files, KILL, CHECKPOINT, SET ONLINE, shrinking, and AG or replication changes are deliberate changes. Change one layer at a time, watch one backup cycle, and check current Microsoft Learn for defaults and version-specific behavior.