My Amazon Relational Database Service (Amazon RDS) database instance on SQL Server is using more space than expected. Why is this happening, and how can I optimize disk storage?

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

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

SELECT AS [database_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.

Be sure you understand how the SQL Server engine uses storage before start 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:


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 free space for transaction logs 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 might 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 can help you evaluate tuning priorities.

Did this page help you? Yes | No

Back to the AWS Support Knowledge Center

Need help? Visit the AWS Support Center.

Published: 2015-10-30

Updated: 2018-04-02