An Amazon RDS database instance running on the SQL Server engine may use more space than expected. Why is this extra space being used and how can I optimize the use of disk storage allocated to my RDS SQL Server DB instance?

You can use the FreeStorageSpace Amazon CloudWatch metric to monitor the available storage space for an RDS DB instance, but it doesn't describe how the SQL Server engine is consuming available storage.

To gather more detailed information about the actual physical disk space usage for a SQL Server database instance, run a query similar to the following:

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 containing ROWS comprise data, and files containing LOGS represent in-flight transactions.

Know how the SQL Server engine uses storage before optimizing. SQL Server engine storage is broadly defined using the following categories:

Database files

The total storage used by an individual database can be broken down into row, index, and free space in the currently active database with a query similar to the following:

EXEC sp_spaceused;

Transaction log files

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

DBCC SQLPERF(LOGSPACE)

Free space in the transaction logs is expected, but you can de-allocate excessive free space by following the instructions at DBCC SHRINKFILE (Transact-SQL).

You can reduce the excessive allocation of transaction log free space by using ALTER DATABASE File and Filegroup Options (Transact-SQL) to configure the auto growth settings for the database.

Temporary database (tempdb)

The SQL Server tempdb grows automatically. If the tempdb is consuming a large amount of available storage, you can shrink it by following the instructions at Shrinking the tempdb Database.

Database indexes

When a significant portion of available storage is dedicated to indexes, you may be able to conserve some space through index tuning. You can gather detailed information about index usage by running the sys.dm_db_index_usage_stats (Transact-SQL) dynamic management view, which may help you evaluate tuning priorities.

Amazon RDS, storage, FreeStorageSpace, excessive storage space, tempdb, transaction log, index tuning


Did this page help you? Yes | No

Back to the AWS Support Knowledge Center

Need help? Visit the AWS Support Center.