Introduction

A production SQL Server database went from roughly 85% to almost 100% transaction log usage in a matter of minutes. The log file had already grown by more than 15 GB. By the time it was checked again, less than 1% of the allocated log was in use, and the disk had plenty of free space.

The immediate pressure had passed, but two questions remained. What caused the log to fill so quickly, and did any application writes fail while it was happening? A healthy reading after the event can’t answer either question on its own.

What a nearly full transaction log means

A log usage alert measures space inside the allocated log file. The amount of free space on the underlying disk is a separate measurement. SQL Server can have plenty of disk space and still experience write delays while the log grows. If it runs out of reusable log space and can’t extend the file, it can raise error 9002. The database can remain online and readable while operations such as order entry, batch processing or integrations fail to write.

1. Check current log usage and what is holding up reuse

Run this read-only query in the affected database. Replace YourDatabase with its name. It shows how much log space is allocated, how much is in use, and the latest reported log reuse wait. Check free space on the volume that hosts the .ldf file at the same time.

USE [YourDatabase];
GO

SELECT
    d.name AS database_name,
    d.recovery_model_desc,
    d.log_reuse_wait_desc,
    d.is_cdc_enabled,
    CAST(l.total_log_size_in_bytes / 1048576.0 AS decimal(18,2)) AS allocated_mb,
    CAST(l.used_log_space_in_bytes / 1048576.0 AS decimal(18,2)) AS used_mb,
    CAST(l.used_log_space_in_percent AS decimal(6,2)) AS used_pct
FROM sys.databases AS d
CROSS JOIN sys.dm_db_log_space_usage AS l
WHERE d.database_id = DB_ID();
Log reuse waitWhere to look next
LOG_BACKUPUnder FULL or BULK_LOGGED recovery, check when the last regular transaction log backup succeeded. Review backup job history for failures, gaps or unusually long runs.
ACTIVE_TRANSACTIONRun DBCC OPENTRAN in the affected database. Identify the oldest open transaction and check whether it is blocked, running a large batch, or sitting idle in an open transaction.
REPLICATIONCheck the replication Log Reader Agent or Change Data Capture (CDC) capture process, as applicable. Review errors and capture lag.
AVAILABILITY_REPLICACheck replica health and whether a secondary is disconnected, suspended or falling behind.
NOTHINGSQL Server currently has reusable log space. If the alert has cleared, use historical evidence to investigate the spike.

Capture the reuse wait while usage is high whenever possible. If you check after the event, the condition that held up reuse may already have cleared.

2. Find out when and how the log grew

In SQL Server Management Studio, check Management → Extended Events → Sessions for an existing session that captured sqlserver.database_file_size_change. If the session wrote to an event file, filter the retained events for the affected log and positive size changes. The is_automatic field helps distinguish autogrowth from a manual resize. Compare timestamps in the same time zone as your monitoring alerts.

If the instance has a default trace with retained history, look for event class 93, which records automatic transaction log growth. Check how often the log grew and how long each extension took. A series of growth events within a few minutes can explain a slowdown even when the volume has ample free space. Default trace history can roll over, and event class 93 covers automatic growth only.

3. Match the growth timeline to backups, jobs and application errors

Start with the first growth event and review the minutes on either side.

  • Backup history. Check whether a regular log backup was overdue, failed, or completed just before usage dropped. A matching timestamp gives you a lead to investigate.

  • Application and maintenance jobs. Look for a bulk import, index rebuild, large batch or integration retry that coincided with the growth. If CDC is enabled, check its capture history for errors and lag.

  • SQL Server and application logs. Look for 9002 for a full transaction log, 5144 for cancelled or timed-out autogrowth, and 5145 for slow autogrowth. Compare those messages with application errors and failed or retried jobs to establish user impact.

Should you shrink the log after the alert clears?

Keep the allocation while you establish what the database needs during its busiest periods. When SQL Server truncates the log, it makes space available for reuse inside the file. Shrinking reduces the physical file size and can force SQL Server to grow it again during the next busy run. Size the log for the workload you observe, and monitor log usage, growth frequency, free disk space and log backup age together.

If your SQL Server logs keep growing or you’re seeing repeated log-space alerts, Onsys can review backup history, log reuse and workload patterns to help identify the cause. Talk to Onsys about a SQL Server health check.