All Products
Search
Document Center

:How to shrink transaction logs in ApsaraDB RDS for SQL Server

Last Updated:May 19, 2022

Overview

This article describes how to shrink transaction logs in ApsaraDB RDS for SQL Server.

Details

Take note of the following items:

  • Before you perform high-risk operations such as modifying the configurations or data of Alibaba Cloud instances, we recommend that you check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
  • You can modify the configurations and data of Alibaba Cloud instances, such as ECS and ApsaraDB RDS instances. We recommend that you create snapshots or enable RDS log backup before you modify instance configurations or data.
  • If you have granted permissions to users or submitted sensitive information such as logon accounts and passwords in Alibaba Cloud Management Console, we recommend that you modify the information in a timely manner.

For more information about the specific operations to shrink transaction logs, see the following sections:

For sufficient log space

If the remaining log space is sufficient, you can shrink transaction logs in the ApsaraDB RDS console. Perform the following steps in the console:

  1. Log on to the ApsaraDB RDS console.
  2. In the top navigation bar, select the region in which the RDS instance resides.
  3. Find your RDS instance and click the ID of the instance. In the left-side navigation pane, click Backup and Restoration.
  4. On the page that appears, click Shrink Transaction Log. Then, click OK.
    Note: The shrinkage takes about 20 minutes to complete. ApsaraDB RDS for SQL Server shrinks transaction logs during each backup.

For insufficient log space

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 run SQL statements to perform the shrinking operation. Transaction logs shrinkage occupies some log space. Therefore, you can only truncate the shrinkage when the transaction log is full. Run the following SQL statements based on the following description. Replace [$DB_Name] in the statements with your database name.

Note: If the transaction log is full, you can only truncate the shrinkage. This has an impact on the data backup restoration to the truncation time. However, this does not affect your business. Back up your database before you run the following SQL statements. If you do not have the permission to back up your database, submit a ticket.

  1. Run the following SQL statements to set the database to the simple mode. Then, the log file is automatically disconnected.
    alter database [$DB_Name] set recovery simple
    Note: [$DB_Name] represents the database name.
  2. Run the following SQL statements to view the status of the log file:
    use [$DB_Name]
    dbcc shrinkfile('[$DB_Name]_log')
  3. Run the following SQL statements to restore the database mode:
    alter database [$DB_Name] set recovery full

References

RDS SQL Server apsaradb for disk space are full

Applicable scope

  • ApsaraDB RDS for SQL Server