All Products
Search

This Product

    ApsaraDB RDS:How to shrink the transaction log for ApsaraDB RDS for SQL Server

    Document Center

    ApsaraDB RDS:How to shrink the transaction log for ApsaraDB RDS for SQL Server

    Last Updated:Jun 18, 2026

    This topic explains how to shrink the transaction log of an ApsaraDB RDS for SQL Server instance with either sufficient or insufficient log space.

    Note

    • Before you perform risky operations, such as modifying an instance or data, verify that the instance has robust disaster recovery and fault tolerance capabilities to protect your data.

    • If you modify the configurations or data of an instance, such as an ECS instance or an ApsaraDB RDS instance, we recommend creating a snapshot or enabling a feature such as log backup for the ApsaraDB RDS instance.

    • If you have granted permissions or submitted security information, such as usernames and passwords, on the Alibaba Cloud platform, we recommend that you change them promptly.

    Sufficient log space

    If the log space is sufficient, you can use the Backup and shrink transaction logs feature on the ApsaraDB RDS console. It automatically performs a log backup and a log shrink operation, optimizing the size of the transaction log file.

    Important

    • When you shrink the transaction log, the system automatically performs a log backup. This archives the transaction log, making a successful cleanup of the local log more likely.

    • Before you shrink the transaction log, check the log reuse wait status of the database.

      • If the status is NOTHING, you can shrink the log. The shrinkable size depends on the size of the reusable virtual log file (VLF) at the end of the transaction log. If an active transaction prevents the VLF at the end of the log from being set to reusable, you may need to perform another log backup. Then, wait for the active transaction to complete and check the reuse wait status again until the status becomes NOTHING.

      • If the status is LOG_BACKUP, the shrink operation may fail due to an active transaction. You may need to perform the shrink operation multiple times to succeed.

      You can go to the Database Management > View Details page to view the log file reuse status (log_reuse_wait_desc).

    1. Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.

    2. In the left-side navigation pane, click Backup and Restoration.

    3. Click Backup and shrink transaction logs, and then click OK.

    4. After the shrink operation is complete, go to the Monitoring and alerts page of the instance to view the latest log space usage.

      On the Standard Monitoring tab, find the Instance Space chart and view the sqlserver.log_size metric to confirm that the log space has been reduced.

    FAQ

    • Q: What should I do if the Backup and shrink transaction logs button is unresponsive?

    • A: This issue usually occurs for the following reasons:

      • The log reuse wait status does not meet the shrink conditions: If the log_reuse_wait_desc status of the database is not NOTHING, the virtual log files (VLFs) in the log file cannot be marked as reusable, which prevents the shrink operation. You can go to the Database Management > View Details page to view the log file reuse status (log_reuse_wait_desc). Retry the operation after the status becomes NOTHING.

      • An active transaction is preventing the VLF at the end of the log from being released: Even if the log_reuse_wait_desc status is NOTHING, the shrink operation cannot release the space if an active transaction is using the VLF at the end of the log. Wait for the active transaction to complete, perform a log backup again, and then click Backup and shrink transaction logs.

    Insufficient log space

    If your database server reports that the "transaction log is full," you cannot shrink the transaction log from the console. You must run SQL statements to do so. Shrinking a transaction log requires some log space. When the log is full, the only option is to break the log chain by running commands.

    Before you begin

    • The following operations are for emergency situations only. We recommend that you first expand the disk space.

    • As a general rule, we do not recommend changing the database recovery model to SIMPLE. This action breaks the ApsaraDB RDS backup chain and causes all subsequent point-in-time restore tasks to fail.

    • For High-availability (HA) edition instances, you must disable database mirroring before you change the database recovery model.

    Warning

    Performing these operations in an emergency means you acknowledge and accept the associated risks. Proceed with caution.

    Procedure

    Basic edition instances

    -- Set the database recovery model to SIMPLE to break the database log chain.
ALTER DATABASE [DatabaseName] SET RECOVERY SIMPLE;

    High-availability (HA) edition instances

    For High-availability (HA) edition instances, database mirroring is active, which blocks direct ALTER DATABASE operations. Follow the steps below:

    -- Disable database mirroring first.
ALTER DATABASE [DatabaseName] SET PARTNER OFF;
GO
-- Set the database recovery model to SIMPLE to break the database log chain.
ALTER DATABASE [DatabaseName] SET RECOVERY SIMPLE;
-- Database mirroring is restored automatically. No manual setup is required.

    Although the system immediately resets the recovery model to FULL and does not permanently change it to SIMPLE, the command still successfully breaks the log chain. The resulting error does not affect this outcome and can be ignored.

    Msg 50000, Level 16, State 1, Procedure ******, Line 46
Login User [Test11] can't change database [TestDb] recovery model.
Msg 3609, Level 16, State 2, Line 2
The transaction ended in the trigger. The batch has been aborted.

    Troubleshooting

    • Q: After I run the ALTER DATABASE [TestDb] SET RECOVERY SIMPLE command, an error message similar to the following appears. How do I fix this?

      Msg 1468, Level 16, State 2, Line 1
The operation cannot be performed on database "zhttestdb" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

    • A: High-availability (HA) edition instances of ApsaraDB RDS for SQL Server use database mirroring, which prohibits ALTER operations on the recovery model. Follow the Procedure to resolve the issue.

    References