Home / Database / Database Transaction Log Full

Database

Database Transaction Log Full

Database operations fail when transaction log (WAL/T-log) cannot grow due to disk space, truncation issues, or growth limits.

Published: Dec 12, 202511 min readBy FixWikiHub Editorial Team

Abstract illustration for a troubleshooting knowledge base category.

Introduction

Transaction logs are essential components of database systems that record all modifications for recovery, replication, and rollback purposes. When transaction logs fill up, databases become unable to process write operations, leading to application failures and potentially data loss. In SQL Server, the transaction log (T-log) can fill due to insufficient autogrowth settings, missing log truncation, or disk space exhaustion. In PostgreSQL, the Write-Ahead Log (WAL) can accumulate when replication stalls or archiving fails. Understanding transaction log management, proper backup configurations, and recovery models is critical for maintaining database availability and preventing log-related outages.

Symptoms

When transaction logs are full, you will observe these symptoms:

Write operations fail with "transaction log full" error messages
Database becomes read-only or unresponsive
Applications report timeout or connection failures on INSERT/UPDATE
Replication stops progressing, showing increasing lag
Disk space monitoring alerts for log drive nearing capacity

SQL Server specific symptoms:

bash

Error: 9002, Severity: 17, State: 2
The transaction log for database 'MyDatabase' is full due to 'LOG_BACKUP'.

Or in application: ``System.Data.SqlClient.SqlException: The transaction log for database 'MyDatabase' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.

PostgreSQL specific symptoms:

bash

ERROR: could not write to file "pg_wal/000000010000000000000001": No space left on device
FATAL: the database system is starting up

Or replication issues: ``WARNING: WAL archival failed: archive_command returned failure ERROR: requested WAL segment 000000010000000000000001 has already been removed

Common Causes

1.Full recovery model without log backups - SQL Server log never truncates
2.Disk space exhaustion - Log drive has no room for growth
3.Autogrowth disabled or too small - Log cannot expand incrementally
4.Long-running active transaction - Log cannot truncate past oldest transaction
5.Replication failure - WAL segments cannot be archived or consumed
6.Log file max size reached - Hard limit on log file size
7.Database mirroring/AG issues - Secondary not consuming log records
8.Bulk operations without simple recovery - Unnecessary log growth
9.Missing checkpoint operations - Log records not marked as reusable
10.Archive command failure - PostgreSQL cannot move WAL files

Step-by-Step Fix

Step 1: Diagnose Log Status (SQL Server)

Check the transaction log status and reason for full condition:

```sql -- Check log space usage DBCC SQLPERF(LOGSPACE);

-- Check why log space cannot be reused SELECT name, log_reuse_wait, log_reuse_wait_desc FROM sys.databases WHERE name = 'MyDatabase';

-- Common log_reuse_wait values: -- LOG_BACKUP: Needs log backup -- ACTIVE_TRANSACTION: Long-running transaction -- CHECKPOINT: Waiting for checkpoint -- DATABASE_MIRRORING: Mirroring issue -- REPLICATION: Replication latency ```

Check current log file size and settings: ``sql -- View log file details SELECT name AS FileName, size/128.0 AS CurrentSizeMB, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS FreeSpaceMB, growth AS GrowthIncrementKB, max_size AS MaxSizeKB FROM sys.database_files WHERE type_desc = 'LOG';

Step 2: Diagnose Log Status (PostgreSQL)

Check WAL status and disk usage:

```bash # Check WAL directory size du -sh $PGDATA/pg_wal

# List WAL files ls -la $PGDATA/pg_wal

# Check current WAL position psql -c "SELECT pg_current_wal_lsn();"

# Check WAL archival status psql -c "SELECT * FROM pg_stat_archiver;" ```

Check replication status if using replication: ```sql -- Check replication slots SELECT slot_name, active, restart_lsn FROM pg_replication_slots;

-- Check WAL sender status SELECT * FROM pg_stat_replication;

-- Calculate replication lag SELECT client_addr, sent_lsn, replay_lsn, pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes FROM pg_stat_replication; ```

Step 3: Take Transaction Log Backup (SQL Server)

If log_reuse_wait shows LOG_BACKUP:

```sql -- Take immediate log backup BACKUP LOG MyDatabase TO DISK = 'D:\Backups\MyDatabase_Log_20241212.trn' WITH COMPRESSION, STATS = 10;

-- Verify backup success SELECT backup_start_date, backup_finish_date, backup_size/1024/1024 AS BackupSizeMB, type FROM msdb.dbo.backupset WHERE database_name = 'MyDatabase' ORDER BY backup_finish_date DESC; ```

If backup fails due to disk space, backup to network share: ``sql BACKUP LOG MyDatabase TO DISK = '\\backupserver\share\MyDatabase_Log.trn' WITH COMPRESSION;

Step 4: Handle Active Transaction Blocking

If long-running transaction prevents truncation:

```sql -- Find the oldest active transaction SELECT t.transaction_id, t.name, t.transaction_begin_time, s.session_id, s.host_name, s.program_name, s.login_name, DB_NAME(s.database_id) AS database_name FROM sys.dm_tran_active_transactions t JOIN sys.dm_tran_session_transactions st ON t.transaction_id = st.transaction_id JOIN sys.dm_exec_sessions s ON st.session_id = s.session_id ORDER BY t.transaction_begin_time;

-- If transaction is blocking, may need to terminate -- First try to identify the process SELECT sqltext.TEXT, req.session_id, req.status, req.command FROM sys.dm_exec_requests req CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) sqltext WHERE req.session_id = <blocking_session_id>;

-- If necessary, kill the blocking session (use with caution) KILL <session_id>; ```

After clearing blocking transaction: ```sql -- Force checkpoint to enable truncation CHECKPOINT;

-- Verify log status DBCC SQLPERF(LOGSPACE); ```

Step 5: Add Additional Log Space (SQL Server)

If disk space is insufficient, add new log file or increase size:

```sql -- Add new log file on different drive ALTER DATABASE MyDatabase ADD LOG FILE ( NAME = 'MyDatabase_Log2', FILENAME = 'E:\Logs\MyDatabase_Log2.ldf', SIZE = 1024MB, MAXSIZE = UNLIMITED, FILEGROWTH = 256MB );

-- Or increase existing file max size ALTER DATABASE MyDatabase MODIFY FILE ( NAME = 'MyDatabase_Log', MAXSIZE = UNLIMITED );

-- Or increase growth increment ALTER DATABASE MyDatabase MODIFY FILE ( NAME = 'MyDatabase_Log', FILEGROWTH = 512MB -- Larger increments reduce VLF fragmentation ); ```

Step 6: Handle PostgreSQL WAL Accumulation

Clear accumulated WAL files in PostgreSQL:

```bash # Check if archival is failing psql -c "SELECT archiver_count, last_archived_wal, last_failed_wal FROM pg_stat_archiver;"

# If archive_command is failing, check the command cat $PGDATA/postgresql.conf | grep archive_command

# Example archive_command archive_command = 'cp %p /backup/wal/%f'

# Verify archive directory exists and is writable ls -la /backup/wal/ mkdir -p /backup/wal chmod 755 /backup/wal ```

If replication slots are accumulating WAL: ```sql -- Check unused replication slots SELECT slot_name, active, restart_lsn, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes FROM pg_replication_slots;

-- Drop unused replication slot (releases retained WAL) SELECT pg_drop_replication_slot('unused_slot_name');

-- Or for logical replication SELECT pg_drop_replication_slot('logical_slot_name'); ```

Manually clear WAL if needed (emergency only): ```bash # Stop PostgreSQL systemctl stop postgresql

# Remove old WAL files manually (be careful!) # Only remove files older than current checkpoint cd $PGDATA/pg_wal ls -lt | head -20 # Check oldest files

# Better approach: Use pg_archivecleanup pg_archivecleanup $PGDATA/pg_wal 000000010000000000000010

# Start PostgreSQL systemctl start postgresql ```

Step 7: Change Recovery Model (SQL Server)

For non-production or databases without point-in-time recovery needs:

```sql -- Check current recovery model SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'MyDatabase';

-- Switch to simple recovery (truncates log on checkpoint) ALTER DATABASE MyDatabase SET RECOVERY SIMPLE;

-- Log will truncate automatically now -- Checkpoint happens automatically, but can force: CHECKPOINT;

-- Verify log space released DBCC SQLPERF(LOGSPACE); ```

Note: Simple recovery model loses point-in-time recovery capability.

Step 8: Configure Regular Log Backups

Set up scheduled transaction log backups:

```sql -- Create maintenance plan or use SQL Agent job -- Example T-SQL for daily full + frequent log backups:

-- Full backup (daily) BACKUP DATABASE MyDatabase TO DISK = 'D:\Backups\MyDatabase_Full.bak' WITH COMPRESSION, INIT;

-- Log backup (every 15 minutes) BACKUP LOG MyDatabase TO DISK = 'D:\Backups\MyDatabase_Log_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ', ''), ':', '') + '.trn' WITH COMPRESSION; ```

Or configure via Ola Hallengren maintenance solution: ``sql -- Using Ola Hallengren's scripts EXECUTE dbo.DatabaseBackup @Databases = 'MyDatabase', @BackupType = 'LOG', @Compress = 'Y', @TimeLimit = 3600, @CleanupTime = 24;

Step 9: Configure PostgreSQL WAL Archival

Properly configure WAL archival in PostgreSQL:

bash

# Edit postgresql.conf
vim $PGDATA/postgresql.conf

Set these parameters: ```ini # WAL configuration wal_level = replica # For replication; use 'logical' for logical replication archive_mode = on # Enable WAL archival archive_command = 'cp %p /backup/wal/%f && find /backup/wal -type f -mtime +7 -delete'

# WAL size management max_wal_size = 2GB # Maximum WAL space min_wal_size = 1GB # Minimum WAL to keep wal_keep_size = 2GB # Keep extra WAL for replication

# Checkpoint tuning checkpoint_timeout = 15min checkpoint_completion_target = 0.9 ```

Reload configuration: ```bash # Reload without restart psql -c "SELECT pg_reload_conf();"

# Or restart for wal_level changes systemctl restart postgresql ```

Verification

After resolving the issue, verify log management is working:

SQL Server: ```sql -- Check log space after fix DBCC SQLPERF(LOGSPACE);

-- Verify backup chain SELECT backup_start_date, type FROM msdb.dbo.backupset WHERE database_name = 'MyDatabase' ORDER BY backup_start_date DESC;

-- Verify log reuse SELECT log_reuse_wait_desc FROM sys.databases WHERE name = 'MyDatabase'; -- Expected: NOTHING or LOG_BACKUP (if backups scheduled) ```

PostgreSQL: ```bash # Check WAL directory size after fix du -sh $PGDATA/pg_wal

# Verify archival is working psql -c "SELECT archiver_count, last_archived_time FROM pg_stat_archiver;"

# Check replication slots psql -c "SELECT slot_name, active FROM pg_replication_slots;" ```

Test write operations: ```sql -- Test insert in SQL Server INSERT INTO TestTable (Col1) VALUES ('Test'); SELECT COUNT(*) FROM TestTable;

-- Test insert in PostgreSQL psql -c "INSERT INTO test_table (col1) VALUES ('test');" psql -c "SELECT COUNT(*) FROM test_table;" ```

Expected: Operations succeed without log full error.

Prevention

To prevent transaction log full issues:

1.Implement regular log backups:
2.- Full backup daily, log backup every 15-30 minutes for full recovery
3.- Use maintenance plans or Ola Hallengren scripts
4.Monitor log space proactively:
5.```sql
6.-- SQL Server monitoring query
7.SELECT name, log_size_mb, log_space_used_pct
8.FROM (
9.SELECT DB_NAME(database_id) AS name,
10.size/128.0 AS log_size_mb,
11.CAST(FILEPROPERTY(name, 'SpaceUsed') AS float)/size*100 AS log_space_used_pct
12.FROM sys.master_files WHERE type_desc = 'LOG'
13.) WHERE log_space_used_pct > 80;
14.`
15.Configure appropriate autogrowth:
16.```sql
17.-- Set growth to 512MB or larger (avoid small increments)
18.ALTER DATABASE MyDatabase MODIFY FILE (
19.NAME = 'MyDatabase_Log',
20.FILEGROWTH = 512MB
21.);
22.`
23.Separate log and data drives: Place log files on dedicated disk for predictable space management.
24.Configure PostgreSQL WAL limits:
25.```ini
26.max_wal_size = 2GB
27.wal_keep_size = 1GB
28.`
29.Monitor replication slot retention:
30.```sql
31.-- PostgreSQL monitoring
32.SELECT slot_name, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes
33.FROM pg_replication_slots;
34.`
35.Set up disk space alerts:
36.```bash
37.# Alert when log drive > 80% full
38.df -h /var/lib/postgresql/data | awk '$5 > 80 {print "ALERT: " $5}'
39.`
40.Use appropriate recovery model: Simple for non-critical, Full with backups for production.
41.Implement log shrink schedule (after ensuring truncation works):
42.```sql
43.-- Only shrink if log is legitimately empty
44.DBCC SHRINKFILE(MyDatabase_Log, 1024); -- Target 1GB
45.`
46.Document backup chain: Maintain documentation of backup schedule and recovery procedures.

[Database troubleshooting: Fix Backup Exclusive Lock Table Production Writes ](backup-exclusive-lock-table-production-writes)
[Fix Connection Pool Leak Application Not Closing Issue in Database](connection-pool-leak-application-not-closing)
[Fix Connection Reset Idle Timeout Firewall Issue in Database](connection-reset-idle-timeout-firewall)
[Fix Connection Reset Idle Timeout Serverless Database Issue in Database](connection-reset-idle-timeout-serverless-database)
[Fix Connection String Encoding Special Characters Issue in Database](connection-string-encoding-special-characters)

Was this guide helpful?

Related search paths

People also search for

If the symptom is close but not identical, these search paths usually surface the right neighboring fixes faster than scrolling the full archive.

Database Transaction Log Full Database Transaction Log Full Database Database Transaction Log Full troubleshooting Database Transaction Log Full fix Database operations fail when transaction log (WAL/T-log) cannot grow due to disk space, truncation issues, or growth limits Database Database operations fail when transaction log (WAL/T-log) cannot grow due to disk space, truncation issues, or growth limits

Explore Related Topics

Browse Guides from Other Categories

Discover troubleshooting guides from related categories to expand your knowledge.

FAQ

Database Troubleshooting FAQs

Common questions about troubleshooting and preventing similar issues

How do I know if this database-errors troubleshooting guide applies to my situation?

This guide is designed for database-errors issues. If you're experiencing similar symptoms described in the article, follow the step-by-step instructions. Start with the most common causes and work through the diagnostic process.

Is it safe to follow these database-errors troubleshooting steps?

Yes, all steps are designed to be safe and non-destructive. We recommend creating backups before making significant changes and testing each step before proceeding to the next.

How long does it typically take to resolve this type of database-errors issue?

Most database-errors issues can be resolved within 30 minutes to 2 hours, depending on the complexity and root cause. Follow the troubleshooting flow to identify and fix the problem efficiently.

How can I prevent this database-errors issue from happening again?

Regular maintenance, monitoring, and following best practices for database-errors configuration can help prevent recurrence. Consider implementing automated checks and alerts for early detection.

Written by

FixWikiHub Editorial Team

Our editorial team consists of experienced DevOps engineers, systems administrators, and cloud architects with hands-on experience in production environments across AWS, Azure, GCP, and on-premises infrastructure.

Every guide undergoes technical review for accuracy and is updated when software versions, commands, or best practices change.

Last updated: Dec 12, 2025

About our team

Important Notice

Disclaimer & Safety Guidelines

The troubleshooting steps in this guide are provided for educational and informational purposes. Before applying any changes to production systems:

Test in a staging environment first — Always verify commands and configurations in a non-production environment before deploying to live systems.
Create backups — Ensure you have current backups of databases, configurations, and critical files before making changes.
Understand the impact — Review how each step may affect your specific environment, dependencies, and users.
Consult official documentation — This guide supplements, but does not replace, official vendor documentation and best practices.

FixWikiHub is not responsible for any damages arising from the use of this content. See our Terms of Use for more information.

Resources

Official Documentation & Further Reading

For authoritative information, consult the official documentation for the technologies discussed in this guide. Our troubleshooting content supplements, but does not replace, vendor documentation.

AWS Documentation — Official Amazon Web Services guides and API references
Kubernetes Documentation — Official Kubernetes documentation
Nginx Documentation — Official Nginx web server documentation
Apache Documentation — Official Apache HTTP Server documentation
Docker Documentation — Official Docker container documentation

Database Transaction Log Full

Introduction

Symptoms

Common Causes

Step-by-Step Fix

Step 1: Diagnose Log Status (SQL Server)

Step 2: Diagnose Log Status (PostgreSQL)

Step 3: Take Transaction Log Backup (SQL Server)

Step 4: Handle Active Transaction Blocking

Step 5: Add Additional Log Space (SQL Server)

Step 6: Handle PostgreSQL WAL Accumulation

Step 7: Change Recovery Model (SQL Server)

Step 8: Configure Regular Log Backups

Step 9: Configure PostgreSQL WAL Archival

Verification

Prevention

People also search for

Browse Guides from Other Categories

WordPress

SSL

DNS

Database Troubleshooting FAQs

FixWikiHub Editorial Team

Disclaimer & Safety Guidelines

Official Documentation & Further Reading

Database Transaction Log Full

Introduction

Symptoms

Common Causes

Step-by-Step Fix

Step 1: Diagnose Log Status (SQL Server)

Step 2: Diagnose Log Status (PostgreSQL)

Step 3: Take Transaction Log Backup (SQL Server)

Step 4: Handle Active Transaction Blocking

Step 5: Add Additional Log Space (SQL Server)

Step 6: Handle PostgreSQL WAL Accumulation

Step 7: Change Recovery Model (SQL Server)

Step 8: Configure Regular Log Backups

Step 9: Configure PostgreSQL WAL Archival

Verification

Prevention

Related Articles

People also search for

Share this guide

More Database Troubleshooting Guides

Browse Guides from Other Categories

Database Troubleshooting FAQs

FixWikiHub Editorial Team

Disclaimer & Safety Guidelines

Official Documentation & Further Reading