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 block 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.

If you have AWS Database Migration Service (AWS DMS) configured for your RDS for SQL Server instance, see Troubleshooting migration tasks in AWS Database Migration Service. If you're troubleshooting SQL Server transaction log growth issues caused by replication lag, see How do I troubleshoot lag in my Amazon RDS for SQL Server read replica? This article covers transaction log file growth caused by long-running transactions.

Note: Don't change the recovery model on Multi-AZ Amazon 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.

Identify the cause of log growth

1. Check log space usage with the following query. This identifies which database is consuming the most log space.

   DBCC SQLPERF(LOGSPACE);

   Example output:

   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.00                0  
   

2. Check the autogrowth configuration with 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                                |  
   

3. Identify what's preventing log truncation with 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  
   ----------|---------------------|--------------------  
   TestDB    | ACTIVE_TRANSACTION  | FULL  
   

   To interpret the output from this query, see Factors that can delay log truncation in the Microsoft SQL Server documentation.

4. Identify long-running transactions with the following query.

   DBCC OPENTRAN('YourDatabaseName');  
   

   Note: Replace YourDatabaseName with the name of your database.

   This command shows the oldest active transaction, the session ID (SPID) holding the transaction, the transaction start time, and the transaction name if you assigned a name. To get more details about the session, query the sys.dm_exec_sessions and sys.dm_exec_requests 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.  
   

5. Get session details with the following query:

   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 s  
   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;  
   

   Note: Replace SPID_FROM_OPENTRAN with the SPID returned by the previous step.

   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'   |  
   

Reclaim log file space

1. If you must terminate the transaction, end the session with the following command.

   KILL SPID;  
   

   Note: Replace SPID with the session ID identified in the preceding section.

   Note: There might be multiple open transactions, and you might need to repeat this step. Before you terminate a transaction, confirm that the business impact of the termination is acceptable. For more information, see sys.dm_tran_active_transactions (Transact-SQL) on the Microsoft Learn website. The length of time required for the rollback increases with the length of time that the query was running.

2. 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  
   

3. After the rollback is complete, run the following command to get the log_reuse_wait_desc status. Wait up to 5 minutes for it to change from LOG_BACKUP to NOTHING.

   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`  
   `----------|---------------------|--------------------`  
   `TestDB``|``  NOTHING    ``  |`` FULL`

4. Run the following query to confirm that "Log Space Used (%)" has decreased.

   DBCC SQLPERF(LOGSPACE);  
   

5. To reclaim the space occupied by the transaction log, shrink the log file with a query similar to the following.

   USE [YourDatabaseName];  
   GO  
   DBCC SHRINKFILE (YourDatabaseName_log, 64);  
   GO  
   

   Note: Replace YourDatabaseName with your database name, and YourDatabaseName_log with the logical name of your log file. Replace the value 64 with your target size in MB.

   Note: Shrink operations shouldn't be considered a regular maintenance operation. Data and log files that grow because of regular business operations don't require shrink operations. Shrink commands impact database performance while they run. They should be run during periods of low usage. For more information see Manage the size of the transaction log file on the Microsoft Learn website.

Preventive measures and best practices

• Create CloudWatch alarms to monitor FreeStorageSpace, and ReplicaLag for read replicas.
• Turn on Amazon RDS storage autoscaling.
• For initial size and autogrowth setting best practices see Manage the size of the transaction log file on the Microsoft Learn website.
• Keep transactions short and break large batch operations into smaller chunks.
• Schedule index maintenance during low-activity periods.

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