How can I troubleshoot storage consumption in my Amazon RDS DB instance that is running SQL Server?

Last updated: 2019-08-13

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

Resolution

You can use the FreeStorageSpace Amazon CloudWatch metric to monitor the available storage space for an RDS DB instance. However, 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 DB 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.

Important: The sys.master_files system view shows the startup size of tempdb, and it doesn't reflect the current size of the tempdb. Run the following query to check the current size of tempdb:

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 storage, be sure that you understand how the SQL Server engine uses storage. 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 Microsoft documentation for DBCC SHRINKFILE.

You can reduce the excessive allocation of free space for transaction logs by using ALTER DATABASE (Transact-SQL) File and Filegroup Options 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 the tempdb database.

Note: If you shrink a tempdb database, check the Message tab in SQL Server Management Studio (SSMS) for error messages after running the command. If you receive a DBCC SHRINKFILE: Page could not be moved because it is a work table page error message, see the Microsoft documentation for DBCC FREESYSTEMCACHE and DBCC FREEPROCCACHE. You can also reboot the DB instance to clear the tempdb. If your DB instance is in a Storage Full status and you can't reboot, you can increase the allocated storage for your DB instance and then reboot. For more information, see How do I resolve problems that occur when Amazon RDS DB instances run out of storage?

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 dynamic management view, which can help you evaluate tuning priorities.