How do I troubleshoot the SQL Server transaction log growth and "Log file full" error in Amazon RDS for SQL Server?

Short description

Your Amazon RDS for SQL Server transaction log might excessively grow or fill up completely, and then cause errors and blocks user transactions. If you have long-running transactions, replication lag, improper autogrowth settings, or insufficient allocated storage, then you might experience the "Log file full" error message. For more information on SQL transaction log files, see Database files and filegroups on the Microsoft Learn website.

Note: Make sure that you don't change the recovery model on Multi-AZ RDS for SQL Server instances because Amazon RDS for SQL Server resets it back to FULL. For more information see Determining a recovery model for your Amazon RDS for SQL Server database.

Resolution

Identify the cause of log growth and then take steps to recover from it.

Troubleshoot excess log growth for Multi-AZ instances

1. To identify what prevents log truncation, run the following query:

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

   Note: Replace YourDatabaseName with the name of your database.

   Example output:

   name              | log_reuse_wait_desc | recovery_model_desc  
   ------------------|---------------------|--------------------  
   Test DB           | ACTIVE_TRANSACTION  | FULL

   Note: To understand the output, see Factors that can delay log truncation on the Microsoft Learn website.

2. If the result for log_reuse_wait_desc is ACTIVE_TRANSACTION, then identify long-running transactions. To see and identify your oldest active transaction, run the following query:

   DBCC OPENTRAN('YourDatabaseName');

   Note: Replace YourDatabaseName with the name of your database.

3. Run the following query to get details about the dynamic management views:
   Example output:

   Transaction information for database 'YourDatabaseName'.  
   Oldest active transaction:  
       SPID (server process ID): 52  
       UID (user ID) : -1  
       Name          : user_transaction  
       LSN           : (123:456:1)  
       Start time    : Jan 16 2026  9:15AM  
       SID           : 0x0105000000000005150000001c00d1bcd181f1492bdfc236f4010000  
   DBCC execution completed. If DBCC printed error messages, contact your system administrator.

   Note: For more information, see sys.dm_exec_sessions (Transact-SQL) and sys.dm_exec_requests (Transact-SQL) on the Microsoft Learn website.

4. Run the following query to get your session details:

   SELECT  
       s.session_id,  
       s.login_name,  
       s.host_name,  
       s.program_name,  
       s.status,  
       s.last_request_start_time,  
       r.command,  
       r.wait_type,  
       t.text AS current_query   
       FROM sys.dm_exec_sessions   
       LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id   
       CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t   
       WHERE s.session_id = [SPID_FROM_OPENTRAN];

   Example output:

   | session_id | login_name | host_name | program_name | status | last_request_start_time | command | wait_type | current_query |  
   |------------|------------|-----------|--------------|--------|------------------------|---------|-----------|---------------|  
   | 52 | app_user | APP-SERVER-01 | Microsoft SQL Server Management Studio - Query | sleeping | 2026-01-16 09:15:23.450 | SELECT | WAITFOR | SELECT * FROM Orders WHERE OrderDate > '2024-01-01' AND Status = 'Pending' |

5. Run the following query to check log space usage:

   DBCC SQLPERF(LOGSPACE);  
   Database Name                                             Log Size (MB)             Log Space Used (%)   Status  
   --------------------------------------------------------- ------------------------- -------------------- -----------  
   master                                                    5.24                      45.23                0  
   tempdb                                                    8.00                      12.50                0  
   model                                                     8.00                      25.00                0  
   TestDB                                                    32000.00                  15.23                0

   Note: Compare the log size against your allocated storage. If log files consume most of your storage, then increase storage or turn on storage automatic scaling.

6. To check your autogrowth configuration, run the following query:

   SELECT  
       DB_NAME(database_id) AS database_name,  
       name AS logical_name,  
       type_desc,  
       size/128.0 AS size_mb,  
       CASE  
           WHEN is_percent_growth = 1 THEN CAST(growth AS VARCHAR) + '%'  
           ELSE CAST(growth/128.0 AS VARCHAR) + ' MB'  
       END AS growth_setting,  
       is_percent_growth  
       FROM sys.master_files   
       WHERE database_id = DB_ID('YourDatabaseName')  
     AND type_desc = 'LOG';

   Note: Replace YourDatabaseName with the name of your database.

   Example output:

   | growth_setting | is_percent_growth | Description | Recommendation |  
   |----------------|-------------------|-------------|----------------|  
   | 10% | 1 | Log grows by 10% of current size each time | Acceptable for small to medium logs |  
   | 512 MB | 0 | Log grows by 512 MB each time | Good for production databases with moderate growth |  
   | 1024 MB | 0 | Log grows by 1 GB each time | Good for large production databases |  
   | 64 MB | 0 | Log grows by 64 MB each time | Too small - can cause performance issues |  
   | 5% | 1 | Log grows by 5% of current size each time | Acceptable for small logs |

7. Monitor FreeStorageSpace, WriteLatency, ReadLatency, and ReplicaLag metrics with Amazon CloudWatch metrics for Amazon RDS.

8. If the output from your log reuse wait query is ACTIVE_TRANSACTION, then identify the blocking transaction. Run the following OPENTRAN query:

   DBCC OPENTRAN('YourDatabaseName');

   Note: Replace YourDatabaseName with the name of your database.

   Example output:

   Transaction information for database 'YourDatabaseName'.  
   Oldest active transaction:  
   SPID (server process ID): 52  
   UID (user ID): -1  
   Name: user_transaction  
   LSN: ([US_SOCIAL_SECURITY_NUMBER])  
   Start time: Jan 20 2026 12:15PM  
   SID: 0x010500000000000515000000a065cf7e784b9b5fe77c87705e060000

9. Stop the blocking transaction with the KILL command:

   KILL SPID;

   Note: Replace SPID with your SPID. You might have multiple open transactions. Before you terminate a transaction confirm that the business impact of the termination is acceptable. For more information, see KILL (Transact-SQL) on the Microsoft Learn website.

10. Monitor rollback progress with the following query:

    SELECT  
        session_id, percent_complete, estimated_completion_time/1000/60   
        AS estimated_minutes_remaining   
        FROM sys.dm_exec_requests  
        WHERE command = 'KILLED/ROLLBACK';

    Example output:

    session_id    percent_complete    estimated_minutes_remaining  
    ----------    ----------------    ---------------------------  
    52            45.67               12.5

To recover from log growth for multi-AZ instances, take the following actions:

1. Turn on Amazon RDS automated backups, with backup retention ≥ 1 day.

2. Make sure that you have no long-running transactions.

3. Check that the replication is healthy.

4. Verify that the Multi-AZ synchronization is healthy.

5. Check that your output from the log_reuse_wait_desc query has a NOTHING status. For more information, see Factors that can delay log truncation on the Microsoft Learn website.

6. To track progress on log file size reduction, run a DBCC SQLPERF query every 5 to 10 minutes until the used log space decreases:

   DBCC SQLPERF(LOGSPACE)

   Note: For more information, see DBCC SQLPERF (Transact-SQL) on the Microsoft Learn website.

7. To plan an appropriate log size and get the log file generation rate, run the following query at regular intervals during peak hours:

   SELECT  
      GETDATE() AS sample_time,  
      name,  
      (total_log_size_in_bytes - used_log_space_in_bytes) / 1024.0 / 1024.0 AS free_space_mb,  
       used_log_space_in_percent   
   FROM sys.dm_db_log_space_usage;

8. Calculate a size to cover 2 to 4 hours of peak activity with a 20% buffer.
   Note: Confirm that the allocated storage accommodates the new file size.

9. Size the log file with a query similar to the following:

   ALTER DATABASE YourDatabaseName MODIFY FILE (  
       NAME = YourDB\_log,  
       SIZE = 8192MB,  
       FILEGROWTH = 512MB);

   Note: Replace YourDatabaseName with the name of your database and replace the SIZE value with an appropriate value.

10. Run the following query to shrink the log file if there are over 500 virtual log files (VLF):

    DBCC SHRINKFILE (YourDB_log, 4096);  _-- 4 GB target example _

    Note: Replace YourDB_log with the name of your log file and replace the 4096 value with an appropriate value. It's a best practice to not use DBCC SHRINKFILE on Multi-AZ instances because it can cause from 30 to 90 minutes of degraded availability. For more information, see DBCC SHRINKFILE (Transact-SQL) on the Microsoft Learn website.

Troubleshoot excess log growth for Single-AZ instances

For single-AZ instances, to reduce and monitor log file growth, complete the following steps:

1. Turn off backup retention.
   Note: When you turn off the backup retention, it can impact your Amazon RDS for SQL Server point-in-time recovery (PITR) and requires downtime.

2. Verify that your database recovery model is set to SIMPLE.

3. Run the following query to shrink the log file:

   DBCC SHRINKFILE (YourDB_log, 1);

4. Run a query to increase disk space for your log file:

   ALTER DATABASE YourDatabaseName MODIFY FILE (NAME = YourDB_log, SIZE = 8192MB);

   Note: Replace YourDatabaseName with the name of your database, YourDB_log with the name of your log file, and the SIZE value with an appropriate value.

5. Turn on backup retention.

6. Set up CloudWatch alarms for FreeStorageSpace, WriteLatency, ReadLatency, ReplicaLag, and CPUUtilization.

7. Run the following query to monitor the log file usage of your allocated disk space:

   SELECT  
   db_name(database_id) as name,  
   (total_log_size_in_bytes / 1024.0 / 1024.0) AS log_size_mb,  
   used_log_space_in_percent  
   FROM sys.dm_db_log_space_usage  
   WHERE used_log_space_in_percent > 80;

   Example output:

   name		log_size_mb	used_log_space_in_percent
   
   ProductionDB	2048.5		87.45
   
   SalesDB		1024.75		92.18
   
   InventoryDB	512.25		83.67
   

Understand key preventive best practices

To prevent log file growth, use the following best practices:

• Use automatic scaling based on absolute file size not a percentage.
• Keep transactions short.
• Break large batch operations into smaller chunks.
• Schedule index maintenance during low-activity periods.
• Use an appropriate DB instance class for the workload.

Related information

Access to transaction log backups with RDS for SQL Server

Determining a recovery model for your Amazon RDS for SQL Server database

Amazon RDS for Microsoft SQL Server database log files

Troubleshooting point-in-time-recovery failures due to a log sequence number gap