SQL Server Transaction Log Full

April 10, 2026

–

There are a handful of alerts that immediately change the tone of your day.

“The transaction log for database ‘X’ is full” is one of them.

When that happens, nothing writes. Applications start throwing errors. Jobs begin failing. Deployments stop mid-flight. In Availability Group environments, secondaries can start drifting behind. It rarely happens quietly.

The mistake most people make at this point is reacting before understanding why the log is full. Growing the file or shrinking it might make the alert disappear, but neither action fixes the underlying reason SQL Server cannot reuse log space.

A full log is not just a storage problem. It is almost always a process problem.

What “Log Full” Actually Means

The log file being full does not necessarily mean the disk is full.

It means SQL Server cannot reuse inactive portions of the log because something is preventing truncation.

That “something” is the key.

Before you change file sizes, kill sessions, or start shrinking, check the reuse wait reason.

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

That one column — log_reuse_wait_desc — tells you where to look next. If you ignore it, you are guessing.

LOG_BACKUP – The Most Common Cause

If the reuse wait shows LOG_BACKUP, you are in FULL or BULK_LOGGED recovery and SQL Server is waiting for a log backup before it can mark VLFs reusable.

In real environments this usually means one of three things:

The SQL Agent job failed.
The job is disabled.
The job is running but writing to a disk that is full or unavailable.

Check the backup history first before doing anything else.

SELECT TOP (5)
    database_name,
    backup_finish_date,
    type
FROM msdb.dbo.backupset
WHERE database_name = 'YourDatabase'
AND type = 'L'
ORDER BY backup_finish_date DESC;

If there has not been a recent log backup, fix the job and run one manually. Do not shrink the file first. That just guarantees it will grow again.

In most cases, a successful log backup immediately frees reusable space.

If it does not, something else is holding the log active.

ACTIVE_TRANSACTION – The Silent Log Killer

If the reuse wait shows ACTIVE_TRANSACTION, SQL Server is protecting log records needed for a transaction that has not committed.

That transaction might not even be doing work anymore. It could be:

A long-running DELETE.
An index rebuild.
An application session that opened a transaction and never closed it.
A forgotten SSMS window from Friday.

Start here:

DBCC OPENTRAN('YourDatabase');

Then look at active requests in that database:

SELECT
    s.session_id,
    s.login_name,
    r.command,
    r.start_time,
    r.status,
    r.wait_type
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s
    ON r.session_id = s.session_id
WHERE r.database_id = DB_ID('YourDatabase');

If you identify the session, do not immediately kill it without thinking through rollback time. A transaction that has been running for an hour may take an hour to roll back. During rollback, the log still cannot be truncated.

If the transaction is a large purge, this is usually where bad DELETE strategy shows up. A single monolithic DELETE in FULL recovery is almost guaranteed to create pressure.

That is why batching matters.

👉 Deleting Rows in Batches in SQL Server
👉 Difference Between DELETE and TRUNCATE in SQL Server (Production Impact)

Those posts exist because large transactions cause incidents.

AVAILABILITY_REPLICA – When HA Is the Bottleneck

In Availability Groups, the primary cannot clear log records until secondaries have hardened what they need.

If log_reuse_wait_desc shows AVAILABILITY_REPLICA, the problem is not on the primary.

It means a secondary is behind.

Check the send and redo queues:

SELECT
    DB_NAME(drs.database_id) AS database_name,
    drs.log_send_queue_size,
    drs.redo_queue_size,
    drs.synchronization_state_desc
FROM sys.dm_hadr_database_replica_states drs
WHERE drs.database_id = DB_ID('YourDatabase');

If redo queue is growing, the secondary may be:

Under I/O pressure
Running large reporting queries
Experiencing network latency
Suspended

Growing the log on the primary may be necessary in the short term, but the real fix is resolving the secondary bottleneck.

If you ignore that and just expand storage, the problem will repeat.

👉 [Placeholder: Understanding Redo Queue in SQL Server Availability Groups]

REPLICATION – Log Reader Lag

If replication is involved and the log reader agent is behind, SQL Server will not truncate required log records.

In that case, the database engine is doing exactly what it should be doing. The issue is downstream.

Check replication agent latency and health before touching the log file.

File Configuration Problems

Sometimes the reuse wait is not the issue. The file simply cannot grow further.

Check the log file configuration:

SELECT 
    name,
    size * 8 / 1024 AS size_mb,
    max_size,
    growth
FROM sys.database_files
WHERE type_desc = 'LOG';

Look for:

Max size restrictions
Tiny autogrowth increments
Disk volume exhaustion

If max size is capped and disk allows expansion, adjust it carefully. Pre-sizing is always better than reactive autogrowth.

What you do not want is 500MB growth increments on a system generating 10GB of log per hour.

The Shrink Question

Shrinking is not inherently wrong. It is just misused constantly.

If the log grew because of:

A one-time data purge
An index rebuild
A migration
A restore operation

Then shrinking back to a sensible baseline can be reasonable once the workload stabilises.

If the log grew because:

Backups were failing
An AG secondary is permanently behind
Application design holds transactions open
Deletes are poorly batched

Shrinking solves nothing. It just sets you up for the same growth event tomorrow.

Only shrink when:

log_reuse_wait_desc is NOTHING
No active transactions are holding space
HA backlog is cleared

And even then, shrink to a realistic steady-state size, not the smallest possible number.

What Actually Causes Most Log Incidents

In real production estates, the patterns repeat:

Log backup job disabled during maintenance and never re-enabled
Large one-shot DELETE during peak hours
Index rebuild of a massive table without planning
AG secondary under-provisioned
Autogrowth set too small

The technical explanation is always slightly different. The operational pattern is usually the same.

Someone changed something without thinking through log impact.

A Better Way to Think About It

When the transaction log is full, the question is not:

“How do I free space quickly?”

The real question is:

“What is SQL Server protecting, and why?”

Until you answer that, any fix is temporary.

Log management is not about reacting to alerts. It is about:

Correct recovery model selection
Reliable log backup cadence
Sensible file sizing
Controlled maintenance operations
Understanding HA dependencies

If you manage production SQL Server instances, especially with Availability Groups, transaction log behaviour is not an edge case. It is central to stability.

Blog Categories

Comments

Leave a Reply Cancel reply