How do I shrink the transaction logs of an ApsaraDB RDS for SQL Server instance? -

This topic describes how to shrink the transaction logs of an ApsaraDB RDS for SQL Server instance.

Note

Take note of the following items:

Before you perform high-risk operations, such as modifying the configurations or data of an instance, we recommend that you check the disaster recovery and fault tolerance capabilities of the instance to ensure data security.
Before you modify the configurations or data of an instance, such as an Elastic Compute Service (ECS) instance or an ApsaraDB RDS instance, we recommend that you create snapshots or enable backup for the instance. For example, you can enable log backup for an RDS instance.
If you have granted permissions on sensitive information or submitted sensitive information in the Alibaba Cloud Management Console, we recommend that you modify the sensitive information at the earliest opportunity. Sensitive information includes usernames and passwords.

Sufficient log storage

If the remaining storage that is used to store logs is sufficient, you can use the shrink transaction log feature in ApsaraDB RDS console. The feature allows the system to automatically perform log backup and log shrinking operations to manage and optimize the size of transaction log files.

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.
In the left-side navigation pane, click Backup and Restoration.
On the page that appears, click Shrink Transaction Log. In the message that appears, click OK.
Important
- When the system shrinks transaction logs, the system automatically performs a log backup to archive transaction logs. This way, on-premises logs are more likely to be successfully deleted.
- We recommend that you check the status of log reuse for your RDS instance before the system shrinks transaction logs.
  - If the status is NOTHING, the log shrinking operation can be performed. However, the size of transaction logs that can be shrunk varies based on the size of reusable virtual log files (VLFs) at the tail of the transaction logs. If the status of tail VLFs cannot be defined as reusable due to existing active transactions, another log backup operation is required. The system waits until the active transactions are complete and checks whether the status of log reuse changes to NOTHING.
  - If the status is LOG_BACKUP, the log shrinking operation may be performed but may fail due to existing active transactions. In this case, the system may perform the log shrinking operation multiple times.

Insufficient log storage

Warning

You can perform the following operations only in emergency. We recommend that you expand the storage capacity of your RDS instance before you perform the following operations. For more information, see Change the specifications of an ApsaraDB RDS for SQL Server instance.

If the server of your database prompts "Transaction log is full", the transaction logs cannot be shrunk in the console. In this case, you must execute SQL statements to perform the shrinking operation. Transaction logs shrinkage occupies some log storage capacity. Therefore, you can only truncate the log chain for shrinkage when the transaction log is full. Execute the following SQL statements on the server of your database. Replace [TestDb] in the statement with your database name.

Important

If the transaction log is full, you can only truncate the log chain for shrinkage. We recommend that you do not change the recovery model of a database to SIMPLE. If you change the recovery model of a database to SIMPLE, the backup chain of the RDS instance to which the database belongs is disconnected, and all restoration tasks that are executed at the point in time when the backup chain is disconnected fail. If you need to change the recovery mode of a database to SIMPLE and truncate the database log chain in an emergency situation, you must understand and are responsible for the preceding risks. After you execute the preceding statements, you can ignore the error messages that are displayed, and the database log chain is disconnected.

For example, you execute the following statements to set the recovery model to SIMPLE:

ALTER DATABASE [TestDb]  //[TestDb] specifies the database name. 
SET RECOVERY SIMPLE

The system fails to set the recovery model of the database to SIMPLE and resets the recovery model to FULL. In this case, the database log chain is disconnected. The system displays the following error messages:

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.

References

Expand the storage capacity of an RDS instance. For more information, see Change the specifications of an ApsaraDB RDS for SQL Server instance.
Use an SQL statement to manage databases. For more information, see Create and manage databases by using SQL statements.
Troubleshoot the error that occurs when the storage capacity of an RDS instance is exhausted. For more information, see What do I do if the storage capacity of an ApsaraDB RDS for SQL Server instance is exhausted?

Application scope

ApsaraDB RDS for SQL Server