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

7 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

To optimize storage consumption, first address immediate storage issues. Then, identify the components that consume space. If your instance is in the Storage Full state, then scale your storage or migrate to a new RDS DB instance with sufficient storage capacity. If your DB instance has available storage, then identify components that consume space and apply optimization techniques for each component.

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 so that you don't run out of storage.

Resolution

Address storage issues for an Amazon RDS for SQL Server instance in the Storage Full state

You can't perform basic operations when your DB instance is stuck in the Storage Full state. For more information, see How do I resolve issues 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 RDS console deactivates.

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

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 DB instance. Then, verify that the new DB instance has provisioned input/output per second (IOPS) or has the General Purpose SSD storage type. Or, use AWS Database Migration Service (AWS DMS) to migrate your data to the new DB instance. For more information, see Using a Microsoft SQL Server database as a source for AWS DMS.

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

describe-valid-db-instance-modifications

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.

For DB instances that have storage autoscaling turned on, automatic storage extension occurs only when specific conditions exist. If autoscaling doesn't extend your storage as expected, then you can manually extend storage. However, note that both automatic and manual storage extension have limitations. For more information about autoscaling conditions and limitations, see Managing capacity automatically with Amazon RDS storage autoscaling.

Analyze storage consumption by database

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 with the type ROWS contain your database data, and files with the type LOGS contain transaction logs for 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;

Understand SQL Server engine storage

To optimize your storage, first identify the components that consume the most space, and understand how the SQL Server engine uses storage. SQL Server engine storage includes the following categories.

Database files

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

use db_name
EXEC sp_spaceused;

Note: Replace db_name with your user database name.

Transaction log files

To determine the amount of storage that the transaction logs use, run the following query:

DBCC SQLPERF(LOGSPACE)

For more details about log space usage for each database, run the following query:

use db_name;

    SELECT 
    DB_NAME() AS [Database Name],
    ls.total_log_size_in_bytes / 1048576.0 AS [Total Log Size in MB],
    ls.used_log_space_in_bytes / 1048576.0 AS [Used Log Size in MB],
    (ls.total_log_size_in_bytes - ls.used_log_space_in_bytes) / 1048576.0 AS [Free Log Space in MB],
    ls.used_log_space_in_percent AS [Used Log Space in %],
    ls.log_space_in_bytes_since_last_backup / 1048576.0 AS [Log Since Last Log Backup in MB],
    dls.log_backup_time AS [Last Log Backup Time]
FROM 
    sys.dm_db_log_space_usage AS ls
CROSS APPLY 
    sys.dm_db_log_stats(DB_ID()) AS dls;

Note: Replace db_name with your database name.

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 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, then 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 check whether index tuning conserves space. To get information about index usage, run the sys.dm_db_index_usage_stats dynamic management view query. For query examples and information about the dynamic management view query, see sys.dm_db_index_usage_stats (Transact-SQL) on the Microsoft website. It's a best practice to remove unused indexes to reclaim space. This can help you evaluate tuning priorities.

Trace files

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

Space consumed by Amazon S3 integration

If you integrated your DB instance with Amazon Simple Storage Service (Amazon S3), then check whether you uploaded files to your D: drive. To check how much space your S3 integration consumes, use the rds_gather_file_details stored procedure and the rds_fn_list_file_details function to list the files on your DB instance. For more information, see Listing files on the RDS DB instance.

CDC

When you turn on change data capture (CDC) for a database, frequent changes to source tables or databases increase the log file size. Storage might run out. If the log disk becomes full, then CDC can't process further transactions.

Auditing

If auditing isn't correctly configured for a DB instance, then 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 DB instance into the Storage Full state. For more information, see Server configuration: c2 audit mode on the Microsoft website.

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