Home / Azure / Fix Azure SQL Database Transaction Log Full

Azure

Fix Azure SQL Database Transaction Log Full

Resolve Azure SQL transaction log full errors by checking backup status, long transactions, and increasing log size.

Published: Apr 3, 20268 min readBy FixWikiHub Editorial Team

Abstract illustration for a troubleshooting knowledge base category.

Introduction

Azure SQL Database transaction log stores all database modifications for recovery and replication. When the log fills up, write operations fail, and the database becomes read-only until log space is freed.

Symptoms

Transaction log full:

sql

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

Write failure:

json

{
  "error": {
    "code": "TransactionLogFull",
    "message": "The transaction log for database 'my-database' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases"
  }
}

Database read-only:

```bash $ az sql db show \ --name my-database \ --server my-server \ --resource-group my-rg \ --query '{State:status,MaxSize:maxSizeBytes}'

# Status shows read-only or limited ```

Common Causes

1.Log backup failure - Backups not running or failing
2.Long-running transaction - Active transaction prevents log reuse
3.Replication lag - Log records needed for replication
4.High transaction volume - Log generation exceeds backup rate
5.Database max size reached - Log cannot grow further
6.Delayed durability - Async log writes accumulating
7.Active checkpoint - Checkpoint blocking log truncation

Step-by-Step Fix

Step 1: Check Log Reuse Wait Reason

```sql -- Identify why log cannot be truncated sqlcmd -S my-server.database.windows.net -d master -U admin -P password -Q " SELECT name, log_reuse_wait_desc, recovery_model_desc, is_encrypted FROM sys.databases WHERE name = 'my-database' "

-- Common log_reuse_wait_desc values: -- NOTHING: No wait, can truncate -- LOG_BACKUP: Waiting for log backup -- ACTIVE_TRANSACTION: Open transaction -- DATABASE_MIRRORING: Replication -- REPLICATION: Transactional replication ```

Step 2: Check Log Size and Usage

```sql -- Check log file size and usage sqlcmd -S my-server.database.windows.net -d my-database -U admin -P password -Q " DBCC SQLPERF(LOGSPACE); "

-- Output shows: -- Database Name | Log Size (MB) | Log Space Used (%) | Status -- my-database | 1024 | 95 | 0

-- If Log Space Used > 90%, action needed ```

Step 3: Trigger Log Backup

```bash # Azure SQL automatically handles backups, but check backup status az sql db show \ --name my-database \ --server my-server \ --resource-group my-rg \ --query '{Backups:backupShortTermRetentionPolicies}'

# For manual log backup (not available in Azure SQL directly) # Instead, ensure automated backups are configured az sql db backup-policy show \ --name my-database \ --server my-server \ --resource-group my-rg ```

Step 4: Find Long-Running Transactions

```sql -- Find active transactions sqlcmd -S my-server.database.windows.net -d my-database -U admin -P password -Q " SELECT t.session_id, t.transaction_id, t.name as transaction_name, t.transaction_begin_time, DATEDIFF(minute, t.transaction_begin_time, GETDATE()) as duration_minutes, s.host_name, s.program_name, s.login_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 "

-- Kill long-running transaction if safe KILL 53; -- session_id from above ```

Step 5: Check Database Max Size

```bash # Check database max size az sql db show \ --name my-database \ --server my-server \ --resource-group my-rg \ --query '{Sku:sku,MaxSizeGB:maxSizeBytes/1073741824}'

# Increase max size if needed az sql db update \ --name my-database \ --server my-server \ --resource-group my-rg \ --max-size 500GB

# Or upgrade to higher tier with more storage az sql db update \ --name my-database \ --server my-server \ --resource-group my-rg \ --sku Premium \ --capacity 4 \ --max-size 1TB ```

Step 6: Monitor Log Generation Rate

```sql -- Check log generation rate sqlcmd -S my-server.database.windows.net -d my-database -U admin -P password -Q " SELECT database_name, log_generation_rate_mb_per_sec, log_backup_rate_mb_per_sec, log_repl_rate_mb_per_sec FROM sys.dm_database_log_rate_stats "

-- If generation_rate > backup_rate, log will fill -- Need to reduce write activity or increase backup frequency ```

Step 7: Check Replication Status

```sql -- If log_reuse_wait_desc = REPLICATION sqlcmd -S my-server.database.windows.net -d master -U admin -P password -Q " SELECT database_id, partner_server, partner_database, state_desc, synchronization_state_desc, replication_lag_sec FROM sys.dm_geo_replication_link_status "

-- If replication lag is high, fix replication issue -- Or temporarily remove replication az sql db replica delete \ --name my-database-secondary \ --server my-server-secondary \ --resource-group my-rg ```

Step 8: Reduce Transaction Size

```sql -- Break large transactions into smaller batches -- Instead of: BEGIN TRANSACTION; DELETE FROM LargeTable WHERE Date < '2020-01-01'; -- Millions of rows COMMIT TRANSACTION;

-- Use batched approach: DECLARE @BatchSize INT = 10000; DECLARE @RowsDeleted INT = 1;

WHILE @RowsDeleted > 0 BEGIN BEGIN TRANSACTION; DELETE TOP (@BatchSize) FROM LargeTable WHERE Date < '2020-01-01'; SET @RowsDeleted = @@ROWCOUNT; COMMIT TRANSACTION; CHECKPOINT; -- Force checkpoint after each batch WAITFOR DELAY '00:00:01'; -- Brief pause between batches END

-- Use minimally logged operations where possible -- BULK INSERT with TABLOCK minimizes logging ```

Step 9: Implement Log Monitoring

```bash # Create alert for high log usage az monitor metrics alert create \ --name sql-log-full-alert \ --resource-group my-rg \ --scopes /subscriptions/SUB/resourceGroups/my-rg/providers/Microsoft.Sql/servers/my-server/databases/my-database \ --condition "avg log_write_percent > 80" \ --window-size 5m

# Monitor log usage over time az monitor metrics list \ --resource /subscriptions/SUB/resourceGroups/my-rg/providers/Microsoft.Sql/servers/my-server/databases/my-database \ --metric "log_write_percent" \ --query 'value[].timeseries[].data[].average' ```

Step 10: Schedule Heavy Operations

```bash # Schedule bulk operations during low activity # Check usage patterns az monitor metrics list \ --resource /subscriptions/SUB/resourceGroups/my-rg/providers/Microsoft.Sql/servers/my-server/databases/my-database \ --metric "cpu_percent" \ --start-time "2024-01-15T00:00:00Z" \ --end-time "2024-01-15T23:59:59Z" \ --interval PT1H

# Schedule during off-peak hours (e.g., 2-6 AM) # Use Azure Automation or scheduled jobs ```

Log Reuse Wait Types

Wait Type	Cause	Resolution
NOTHING	No wait	Normal state
LOG_BACKUP	Need backup	Check backup job
ACTIVE_TRANSACTION	Open transaction	Kill or commit transaction
REPLICATION	Replication lag	Fix replication
DATABASE_MIRRORING	Mirroring lag	Check mirror status
CHECKPOINT	Checkpoint running	Wait or force checkpoint

Verification

```sql -- After resolving log issue sqlcmd -S my-server.database.windows.net -d my-database -U admin -P password -Q " DBCC SQLPERF(LOGSPACE); "

-- Log Space Used should be < 70%

-- Verify database writable sqlcmd -S my-server.database.windows.net -d my-database -U admin -P password -Q " INSERT INTO TestTable (Value) VALUES ('test'); SELECT * FROM TestTable WHERE Value = 'test'; DELETE FROM TestTable WHERE Value = 'test'; "

-- Should complete without errors

-- Monitor for 24 hours sqlcmd -S my-server.database.windows.net -d my-database -U admin -P password -Q " SELECT log_reuse_wait_desc, AVG(log_space_used_percent) as avg_log_used FROM sys.dm_db_log_space_usage GROUP BY log_reuse_wait_desc " ```

Prevention

To prevent Azure SQL transaction log full issues from recurring, implement these proactive measures:

1. Monitor Log Space Usage

yaml

groups:
- name: azure-sql-log
  rules:
  - alert: AzureSQLLogSpaceHigh
    expr: |
      azure_sql_log_space_used_percent > 80
    for: 10m
    labels:
      severity: warning
    annotations:
      summary: "Azure SQL transaction log usage exceeds 80%"

2. Configure Log Size Appropriately

```sql -- Check current log size SELECT name, size/128.0 AS CurrentSizeMB, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS FreeSpaceMB FROM sys.database_files WHERE type = 1; -- Log file

-- Increase log file size ALTER DATABASE my-database MODIFY FILE (NAME = my-database_log, SIZE = 10GB);

-- Enable auto-growth ALTER DATABASE my-database MODIFY FILE (NAME = my-database_log, FILEGROWTH = 1GB); ```

3. Schedule Regular Log Backups

```sql -- For Full recovery model, schedule transaction log backups -- Every 15 minutes for active databases

-- Check recovery model SELECT name, recovery_model_desc FROM sys.databases;

-- Set recovery model if needed ALTER DATABASE my-database SET RECOVERY FULL;

-- Create log backup schedule (via Azure Automation or SQL Agent) BACKUP LOG my-database TO URL = 'https://mystorage.blob.core.windows.net/backups/log.bak'; ```

Best Practices Checklist

[ ] Monitor log space usage
[ ] Configure appropriate log size
[ ] Schedule regular log backups
[ ] Check for long-running transactions
[ ] Review recovery model
[ ] Monitor replication lag

[Fix Azure SQL Database DTU Limit](/articles/fix-azure-sql-database-dtu-limit)
[Fix Azure SQL Database Deadlock](/articles/fix-azure-sql-database-deadlock)
[Fix Azure SQL Database Geo-Replica Lag](/articles/fix-azure-sql-database-geo-replica-lag)

[Technical troubleshooting: Fix Azure Aks Pod Crashloopbackoff Issue in Azure](azure-aks-pod-crashloopbackoff)
[Technical troubleshooting: Fix Azure Api Management Policy Expression Runtime](azure-api-management-policy-expression-runtime-error)
[Technical troubleshooting: Fix Azure App Configuration Feature Flag Not Refre](azure-app-configuration-feature-flag-not-refreshing)
[Technical troubleshooting: Fix Azure App Service 503 Always On Disabled Issue](azure-app-service-503-always-on-disabled)
[Technical troubleshooting: Fix Azure Application Gateway Err SSL Unrecognized](azure-application-gateway-err-ssl-unrecognized-name-alert)

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.

Azure SQL Database Transaction Log Full Azure SQL Database Transaction Log Full Azure Azure SQL Database Transaction Log Full troubleshooting Azure SQL Database Transaction Log Full fix Resolve Azure SQL transaction log full errors by checking backup status, long transactions, and increasing log size Azure Resolve Azure SQL transaction log full errors by checking backup status, long transactions, and increasing log size

Explore Related Topics

Browse Guides from Other Categories

Discover troubleshooting guides from related categories to expand your knowledge.

FAQ

Azure Troubleshooting FAQs

Common questions about troubleshooting and preventing similar issues

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

This guide is designed for azure-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 azure-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 azure-errors issue?

Most azure-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 azure-errors issue from happening again?

Regular maintenance, monitoring, and following best practices for azure-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: Apr 3, 2026

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

Fix Azure SQL Database Transaction Log Full

Introduction

Symptoms

Common Causes

Step-by-Step Fix

Step 1: Check Log Reuse Wait Reason

Step 2: Check Log Size and Usage

Step 3: Trigger Log Backup

Step 4: Find Long-Running Transactions

Step 5: Check Database Max Size

Step 6: Monitor Log Generation Rate

Step 7: Check Replication Status

Step 8: Reduce Transaction Size

Step 9: Implement Log Monitoring

Step 10: Schedule Heavy Operations

Log Reuse Wait Types

Verification

Prevention

1. Monitor Log Space Usage

2. Configure Log Size Appropriately

3. Schedule Regular Log Backups

Best Practices Checklist

People also search for

Browse Guides from Other Categories

WordPress

SSL

DNS

Azure Troubleshooting FAQs

FixWikiHub Editorial Team

Disclaimer & Safety Guidelines

Official Documentation & Further Reading

Fix Azure SQL Database Transaction Log Full

Introduction

Symptoms

Common Causes

Step-by-Step Fix

Step 1: Check Log Reuse Wait Reason

Step 2: Check Log Size and Usage

Step 3: Trigger Log Backup

Step 4: Find Long-Running Transactions

Step 5: Check Database Max Size

Step 6: Monitor Log Generation Rate

Step 7: Check Replication Status

Step 8: Reduce Transaction Size

Step 9: Implement Log Monitoring

Step 10: Schedule Heavy Operations

Log Reuse Wait Types

Verification

Prevention

1. Monitor Log Space Usage

2. Configure Log Size Appropriately

3. Schedule Regular Log Backups

Best Practices Checklist

Related Issues

Related Articles

People also search for

Share this guide

More Azure Troubleshooting Guides

Browse Guides from Other Categories

Azure Troubleshooting FAQs

FixWikiHub Editorial Team

Disclaimer & Safety Guidelines

Official Documentation & Further Reading