For a full or rapidly growing SQL transaction log, first measure used log space, the reuse wait, recent log backups, open transactions and the file/disk limits. Repair the dependency identified by that evidence; capacity relief may be needed while it is repaired. Do not delete the LDF, switch recovery model or schedule shrink as a shortcut.
This guide uses SQL Server 2022 as its reference. It is source-researched and untested. The diagnostic query reads existing metadata; no log was shrunk, transaction terminated or recovery model changed for this article. Rehearse collection and any subsequent remedy in an approved lab.
Understand log reuse before changing file size
SQL Server records changes in its transaction log so that it can recover the database and support the configured protection features. Log truncation makes eligible inactive log space available for reuse. It does not necessarily reduce the physical LDF file on the operating system.
Shrinking is a different operation: it attempts to return eligible space from the physical file to the operating system. Microsoft’s log-size guidance explicitly distinguishes these concerns and warns against treating shrink as regular maintenance. If the workload repeatedly needs the same capacity, shrinking it away only creates another growth cycle.
An LDF that remains large after a successful log backup is therefore not automatically faulty. It may contain reusable capacity intentionally retained for the next workload. Conversely, a small amount of Windows free space can be urgent even if much of the database’s existing log allocation is reusable.
Record these measurements together. A screenshot of the LDF size without log-usage and growth information is insufficient to decide whether the database needs more space, a failed dependency repaired or no immediate file change.
Capture the exact failure and current state
Preserve the SQL error, database name, time and affected application operation. Error 9002 indicates a full transaction log, but the cause needs investigation. Microsoft’s 9002 guidance covers log reuse blockers, disk capacity and file-growth restrictions.
Record the current recovery model, backup schedule, recent workload changes and any availability or replication configuration. Check whether an unusual import, index maintenance task or long-running application transaction overlaps the growth period. Treat the overlap as a lead, not proof.
An authorised DBA can run this author-written read-only inventory example. It has not been executed here. Visibility depends on the login’s approved permissions; missing rows must not be interpreted as proof that a database is absent.
SELECT name, state_desc, recovery_model_desc,
log_reuse_wait_desc
FROM sys.databases
WHERE name = N'LabOrders';
The field definitions are documented in sys.databases. Preserve the result with its observation time. The reuse reason can change as work completes, so one observation describes a moment rather than the whole incident.
In the database’s supported reports or approved monitoring, inspect used log space. Inspect each log file’s current size, maximum size and growth configuration. Microsoft identifies sys.dm_db_log_space_usage and the size, max_size and growth fields in sys.database_files as relevant sources S3. Have the DBA use version-appropriate permissions and established collection tooling rather than granting broad access just to run a diagnostic.
Collect a compact diagnostic set
Untested SQL Server 2022 checks. Run the database-specific statements in the approved affected database, illustrated by LabOrders. The log-space DMV needs VIEW SERVER PERFORMANCE STATE; DBCC OPENTRAN needs an existing db_owner or sysadmin role. History/catalogue visibility must also be authorised R1, R2, R3, R4. These statements inspect state; no backup, growth, cancellation or repair runs.
USE [LabOrders];
SELECT total_log_size_in_bytes, used_log_space_in_bytes,
used_log_space_in_percent
FROM sys.dm_db_log_space_usage;
SELECT name, physical_name, size / 128.0 AS size_MiB,
max_size, growth, is_percent_growth
FROM sys.database_files WHERE type_desc = N'LOG';
SELECT MAX(backup_finish_date) AS last_recorded_log_backup
FROM msdb.dbo.backupset
WHERE database_name = N'LabOrders' AND type = 'L';
DBCC OPENTRAN (N'LabOrders');
size and non-percentage growth use 8 KiB pages; max_size has special values, including 0 for no growth and -1 for growth subject to remaining disk capacity and engine limits. Do not multiply those sentinels into a capacity figure R2. History does not prove media remains accessible. OPENTRAN identifies the oldest active transaction, not a session to kill R3. Compare the log's actual Windows volume with the capacity checks in the companion article.
| Evidence | DBA decision before a change |
|---|---|
| LOG_BACKUP | Restore the owning log-backup job/path and preserve its chain. |
| ACTIVE_TRANSACTION | Identify the owner and progress; decide completion or cancellation with them. |
| Availability/replication wait | Repair that consumer path; another log backup may not help. |
| NOTHING, yet allocation fails | Check maximum size, autogrowth and physical capacity. |
| CHECKPOINT, LOG_SCAN or DATABASE_SNAPSHOT_CREATION | Can be brief processing waits; correlate duration and other evidence before intervening. |
During an outage, DBA-approved growth or extra capacity can be temporary relief. Adding another log file is a separate supported recovery choice, not the first diagnostic action S1.
Follow the reported reuse blocker
When the reason is LOG_BACKUP, inspect the transaction-log backup arrangement for databases using the full or bulk-logged recovery model. Confirm that a log backup actually ran, completed and produced accessible media. A successful full database backup is not evidence that the log-backup job ran.
Check ownership before starting an additional backup workflow. Another tool may already own the log chain, destination and retention. The DBA should determine the supported response and preserve all resulting recovery dependencies. A newly successful log backup is only one part of restoring the intended protection arrangement.
When the reason is ACTIVE_TRANSACTION, identify the transaction and its owner. Record when it began, what application work it belongs to and whether it is progressing. A long transaction can retain log records even while other transactions finish and backups run S1.
Do not terminate the largest or oldest session merely because it is visible. Cancellation can require rollback, affect users and leave business reconciliation work. The application owner and DBA must decide whether the transaction should finish, be corrected or be deliberately stopped. Preserve evidence before that decision.
When the reason identifies an availability replica or replication dependency, investigate that protection path. Review the relevant queues, connection health and paused or failed components through the established availability runbook. Adding a routine log backup does not resolve every downstream consumer that still needs log records.
A different reason, or a reason that changes between observations, calls for its documented investigation. Do not build an automatic repair script that treats every non-empty value as an instruction to shrink, kill a session or switch recovery model. The field identifies what SQL Server is waiting for; it does not grant authority to remove the dependency.
Distinguish a reuse problem from a capacity problem
If reusable log space is available but growth fails, inspect file maximums, autogrowth configuration and the Windows volume. If the volume is full, map it to its virtual disk and datastore before asking for expansion. Free space somewhere else in the VMware environment does not automatically exist inside this filesystem.
A virtual disk can have unused capacity beyond its current partition. Equally, a thin-provisioned disk can advertise guest capacity while its backing storage has inadequate headroom. Keep those cases separate from SQL’s logical reuse blocker. The companion guest-capacity article explains the mapping.
Check the workload’s storage response as well as free bytes. A log destination must support the required write workload. Moving the problem to a large but unsuitable volume can replace space failures with latency failures. Microsoft’s size guidance specifically cautions against overloading log storage S3.
Do not use an arbitrary percentage of database size as a universal log allocation. Transaction size, workload bursts, backup intervals and the time needed to resolve protection delays all affect requirements. Measure occupied log space across the full-backup window, the largest index-maintenance operation and the largest batch, as Microsoft recommends S3. Include the observed peak plus an explicitly accepted reserve.
Plan the remedy at the layer that failed
If log backups failed because of destination access or capacity, repair that backup path with its owner. Verify that the intended sequence is preserved and that later recovery remains possible. If an application transaction retains the log, address that transaction and its design. If a replica is behind, resolve the replica path without casually removing protection.
If more physical capacity is required, have the SQL and platform owners review it together. Record the intended file size, growth setting, Windows volume and underlying headroom. A file-growth change is a state-changing operation even when it seems routine.
The original and proposed settings should be recorded, but reversal is not always a simple return to the old size. A database that has used the new capacity may no longer fit in its previous allocation. Rollback planning must consider data and active-log requirements, not just restore a configuration value.
This article intentionally supplies no executable shrink, recovery-model change, session termination or additional-log-file command. Microsoft’s documentation describes possible remedies, but selection depends on the diagnosed reason and the recovery obligations of the actual database.
Verify the original fault has gone
After an approved remedy, observe log usage, the reuse reason and physical free space through a representative workload interval. Confirm that the operation which originally failed now succeeds. Retain both normal and exceptional periods so the result is not based solely on a quiet server.
Verify that scheduled log backups continue, required replicas or consumers remain healthy, and the file is not repeatedly growing into the same boundary. If capacity alone was added while the reuse blocker remains unresolved, report that as temporary relief with a named corrective action.
Check recoverability using an isolated restoration of the relevant backup sequence. A new green backup result does not establish that all media and keys required by the sequence remain available. Keep that recovery check separate from the application’s immediate return to service.
Finally, assign monitoring to the normal operational tooling. Useful alerts combine used log capacity, growth trend, destination space and protection failures. Use an automated collector with bounded execution and recorded timestamps rather than a person repeatedly running queries.
What to avoid
- Do not delete or detach the active LDF file to reclaim Windows space.
- Do not change to simple recovery merely to make a symptom disappear without accepting the protection consequences.
- Do not shrink on a repeating schedule to disguise predictable workload growth.
- Do not claim a full backup fixed a log-backup failure without checking the actual log workflow.
Limitations
The examples target SQL Server 2022 and do not diagnose every replica, replication or transaction fault. An instantaneous reuse wait is not a full incident history. Backup history, successful allocation and successful restoration are separate evidence.
Acceptance checklist
- Used bytes, reuse wait, last recorded log backup and oldest transaction captured.
- File growth/max settings and the correct Windows volume headroom checked.
- Owner-approved remedy restored writes without silently reducing protection.
- Representative workload and log-backup/recovery evidence confirm the result.
Learning notes
- Logical log reuse and physical file shrinking are separate operations.
- The current reuse reason directs the investigation; it is not an automatic repair instruction.
- Capacity relief is incomplete if the blocking transaction or protection fault remains.
Related reading: SQL Point-in-Time Restore: Plan and Verify the Backup Chain; Windows Disk Full, VMware Space Free: Map the Layers; How to optimise SQL Server on VMware using waits and measured I/O.
Sources and review status
Sources accessed 30 October 2026. Exact displayed dates, scope and claim mapping are in the accompanying evidence file. Revised after independent review; independent recheck pending. All examples remain untested and no lab validation is claimed.
- Troubleshoot Full Transaction Log Error 9002 - SQL Server | Microsoft Learn
- sys.databases (Transact-SQL) - SQL Server | Microsoft Learn
- Manage Transaction Log File Size - SQL Server | Microsoft Learn
Additional revision sources: