How do I optimize storage consumption in my Amazon RDS for SQL Server DB instance?

6 minute read

My Amazon Relational Database Service (Amazon RDS) for Microsoft SQL Server DB instance uses more space than expected. I want to optimize my disk storage.

Short description

You can use the FreeStorageSpace metric in Amazon CloudWatch to monitor available storage space for a DB instance. The FreeStorageSpace metric doesn't describe how the SQL Server engine uses the available storage. Monitor this metric frequently and turn on storage autoscaling to make sure you don't run out of storage.

Resolution

Note: If you receive errors when you run AWS Command Line Interface (AWS CLI) commands, then see Troubleshooting errors for the AWS CLI. Also, make sure that you're using the most recent AWS CLI version.

Amazon RDS for SQL Server instance is in the Storage Full state

You can't perform basic operations when your Amazon RDS instance is stuck in the Storage Full state. For more information, see How do I resolve problems that occur when Amazon RDS DB instances run out of storage?

Some RDS for SQL Server DB instances have limitations on how you can modify storage. If your DB instance isn't eligible for modification, then the Allocated storage option in the Amazon RDS console is deactivated. To scale storage on an instance when the modify option isn't available, use native backup and restore to migrate your data to a new instance. Make sure that the new instance has provisioned input/output per second (IOPS) or has the General Purpose (SSD) storage type. Or, use a data migration tool to migrate to the new instance. For more information, see Modifying an Amazon RDS DB instance.

To see the valid storage options for your DB instance, run the describe-valid-db-instance-modifications command:

describe-valid-db-instance-modifications

Note: Scale storage and storage autoscaling aren't supported in RDS for SQL Server instances that use magnetic storage.

For instances that have storage autoscaling turned on, you can extend storage only in certain scenarios. For more information, see Managing capacity automatically with Amazon RDS storage autoscaling. Additionally, you can only extend storage when the maximum storage threshold doesn't equal or exceed the storage increment. For more information, see Limitations.

Storage consumption for RDS for SQL Server instances

To get information about the physical disk space usage for your RDS for SQL Server DB instance, run a query similar to the following example:

SELECT D.name AS [database_name]    
    , F.name AS [file_name]
    , F.type_desc AS [file_type]
    , CONVERT(decimal(10,2), F.size * 0.0078125) AS [size_on_disk_mb]
    , CONVERT(decimal(10,2), F.max_size * 0.0078125) AS [max_size_mb]
FROM sys.master_files AS F
INNER JOIN sys.databases AS D
    ON F.database_id = D.database_id;

Files that contain ROWS comprise data, and files that contain LOGS represent in-flight transactions.

For more information, see sys.master_files (Transact-SQL) on the Microsoft website.

Note: The sys.master_files system view shows the startup size of tempdb. It doesn't reflect the current size of the tempdb.

To check the current size of tempdb, run the following query:

select name AS [database_name], physical_name AS [file_name], 
convert(decimal(10,2),size*0.0078125) AS [size_on_disk_mb]
from tempdb.sys.database_files;

Before you optimize your storage, make sure that you understand how the SQL Server engine uses storage. SQL Server engine storage is broadly defined in the following categories:

Database files

You can break down the total storage used by an individual database into row, index, and free space in the currently active database. To breakdown the total storage, run the following query:

EXEC sp_spaceused;

Transaction log files

To determine the amount of storage used by transaction logs, run the following query:

DBCC SQLPERF(LOGSPACE)

You might see free space in the transaction logs. To deallocate excessive free space, run the DBCC SHRINKFILE command. For more information, see DBCC SHRINKFILE (Transact-SQL) on the Microsoft website.

To reduce the excessive allocation of free space for transaction logs use the ALTER DATABASE (transact-SQL) file and filegroup options. The options configure the autogrowth settings for the database. For more information, see ALTER DATABASE (Transact-SQL) File and Filegroup Options on the Microsoft website.

Temporary database (tempdb)

The SQL Server tempdb grows automatically. If the tempdb consumes a large amount of available storage, then you can shrink the tempdb database.

Note: If you shrink a tempdb database, then check the Message tab in SQL Server Management Studio (SSMS) for error messages after you run the command.

If you receive a "DBCC SHRINKFILE: Page could not be moved because it is a work table page" error message, then see DBCC FREESYSTEMCACHE (Transact-SQL) and DBCC FREEPROCCACHE (Transact-SQL) on the Microsoft website. You can also reboot the DB instance to clear the tempdb.

DB instances in a Storage Full state might fail to reboot. If this occurs, increase the allocated storage for your DB instance and then try to reboot again. For more information, see How do I resolve problems that occur when Amazon RDS DB instances run out of storage?

Database indexes

If you dedicate a large amount of your available storage to indexes, then you might be able to conserve some space through index tuning. To get information about index usage, run the sys.dm_db_index_usage_stats dynamic management view. This can help you evaluate tuning priorities. For more information, see sys.dm_db_index_usage_stats (Transact-SQL) on the Microsoft website.

Trace files

Trace files, including C2 Audit Trace files and dump files, can consume a large amount of disk space. Amazon RDS automatically deletes trace and dump files older than 7 days, but you can also adjust the retention settings for your trace files. For more information, see Setting the retention period for trace and dump files.

Space consumed by Amazon S3 integration

If you integrated your RDS DB instance with Amazon S3, you might have uploaded files to your D: drive that take up space. To check how much space is taken up by your S3 integration, run a command to list the files on your DB instance. For more information, see Listing files on the RDS DB instance.

CDC

For databases that have CDC turned on, log file size increases based on the frequency of changes to the source tables or databases. Storage might eventually run out. If the log disk becomes full, then CDC can't process further transactions.

Auditing

If auditing isn't configured correctly for an instance, the logs might grow exponentially and affect storage. For more information, see SQL Server Audit.

C2 audit mode saves a large amount of event information to the log file. The log file might grow quickly and put the instance into the Storage Full state. For more information, see C2 audit mode (server configuration option) on the Microsoft website.

Additionally, if you turn on features such as query store, then your resource utilization might be affected.