Shrinking Databases

Scenarios

You can use stored procedures to shrink the size of the data and log files in a specified database.

• rds_shrink_database: shrinks all files of a specified database.
• rds_shrink_database_log: shrinks log files of a specified database.

For more operations, see Shrinking an RDS for SQL Server Database.

Prerequisites

• Before shrinking a database, ensure that your instance has sufficient storage space.
• Your RDS for SQL Server DB instance has been connected. You can connect to the DB instance through the SQL Server client. For details, see Connecting to a DB Instance Through a Public Network.

Shrinking a Database

1. Run the following command to shrink the database:

   EXEC [master].[dbo].[rds_shrink_database] @DBName='myDbName';

   Table 1 Parameter description

   Parameter	Description
   myDbName	Name of the database to be shrunk. If this parameter is not specified, all databases are shrunk by default.

2. After the command is successfully executed, the following information is displayed:

   HW_RDS_Process_Successful: Shrink Database Done.

Shrinking Database Log Files

Run the following command to shrink log files of a specified database:

EXEC [master].[dbo].[rds_shrink_database_log] @dbname;

@dbname: indicates the name of the database whose log files are to be shrunk.

Example

1. Run the following command to shrink the dbtest2 database:

   EXEC [master].[dbo].[rds_shrink_database] @DBName = 'dbtest2';

   The command output is as follows.

   

2. Run the following command to shrink all databases:

   EXEC [master].[dbo].[rds_shrink_database];

3. Run the following command to shrink the log files of the testdb database:

   EXEC [master].[dbo].[rds_shrink_database_log]@dbname='dbtest3';

FAQs

1. If an error message indicating that the log file is in use is displayed during the execution of the stored procedure, run the stored procedure later.
2. If the log file size is not changed after the stored procedure is executed, run the following SQL statement in the database to check whether there is enough available space in the log file:

   SELECT name, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files WHERE type_desc='LOG';