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 a SQL Server client. For details, see Connecting to a DB Instance Through a Public Network.

Shrinking a Database

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.

After the command is successfully executed, the following information is displayed:
```
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

Run the following command to shrink the dbtest2 database:
EXEC [master].[dbo].[rds_shrink_database] @DBName = 'test';

The command output is as follows.
Run the following command to shrink all databases:
EXEC [master].[dbo].[rds_shrink_database];
Run the following command to shrink the log files of the testdb database:
EXEC [master].[dbo].[rds_shrink_database_log]@dbname='dbtest3';

FAQs

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.
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';
```

If the log file size does not change after the stored procedure for log shrinking has been executed multiple times, the log file is in use. Run the following SQL statement to check whether the log file is being used:

SELECT name, log_reuse_wait_desc FROM sys.databases where name='test';

If the log file is being used, wait for a period of time and then shrink it again.

**Table 2** log_reuse_wait_desc description
log_reuse_wait_desc Value	Description
NOTHING	There are one or more reusable virtual log files (VLFs).
CHECKPOINT	Checkpoints have not been generated since the last log truncation, or the log header has not been moved across VLFs (all recovery models).
LOG_BACKUP	Before the transaction log is truncated, it needs to be backed up.
ACTIVE_BACKUP_OR_RESTORE	Data is being backed up or restored.
ACTIVE_TRANSACTION	The transaction is active.
DATABASE_MIRRORING	Database mirroring is suspended, or the mirror database lags behind the principal database in high-performance mode.
REPLICATION	During transaction replication, the transaction related to the publication is still not delivered to the distribution database.
DATABASE_SNAPSHOT_CREATION	A database snapshot is being created.
LOG_SCAN	Log scanning is in progress.
AVAILABILITY_REPLICA	The secondary replica of an availability group is applying the transaction log records of this database to the corresponding secondary database.