Shrinking an RDS for SQL Server Database

Scenarios

You can use a stored procedure to shrink the size of the data and log files in a specified RDS for SQL Server database.

Prerequisites

An RDS for SQL Server DB instance has been connected. Connect to the DB instance through the Microsoft SQL Server client. For details, see Connecting to a DB Instance Through a Public Network.

Constraints

• The database can be shrunk only when the database file size exceeds 50 MB. Otherwise, the following message is displayed:

  

• Transactions running at the row version control-based isolation level may prevent shrinking operations. To solve this problem, perform the following steps:
  1. Terminate the transactions that prevent shrinking.
  2. Terminate the shrinking operation. All completed tasks will be retained.
  3. Do not perform any operations and wait until the blocking transactions are complete.

Best Practices

When you plan to shrink a database, consider the following:

• A shrink operation is most effective after an operation that creates lots of unused space, such as a database reboot.
• Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.
• A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.

Procedure

1. Run the following command to shrink a 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.

   The following figure shows the execution result set. Each result corresponds to the information about each file in the specified database (or all databases).

   Figure 1 Result set
   

   Table 2 Result set parameter description

   Column Name	Description
   DbId	Database ID of the current shrink file.
   FileId	File ID of the current shrink file.
   CurrentSize	Number of 8 KB pages occupied by the file.
   MinimumSize	Minimum number of 8 KB pages occupied by the file. The value indicates the minimum size or the initial size of the file.
   UsedPages	Number of 8 KB pages used by the file.
   EstimatedPages	Number of 8 KB pages that the database engine estimates the file can be shrunk to.

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

   HW_RDS_Process_Successful: Shrink Database Done.

Fault Rectification

If the file size does not change after the database is shrunk, run the following SQL statement to check whether the file has sufficient available space:

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

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.

   Figure 2 Execution result
   
2. Run the following command to shrink all databases:

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