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

Last updated: 2020-09-22

My Amazon Relational Database Service (Amazon RDS) DB 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.

Note: The sys.master_files system view shows the startup size of tempdb. 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

You can break down the total storage used by an individual database into row, index, and free space in the currently active database by running 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)

You can expect free space in the transaction logs, 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

If you are dedicating a significant portion of your available storage 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. This can help you evaluate tuning priorities.

Trace files

Trace files, including C2 Audit Trace files and dump files, can consume a lot 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 Working with trace and dump files.

Space consumed by Amazon S3 integration

If you have integrated your RDS DB instance with Amazon S3, you may have uploaded files to your D: drive that are taking up space. To check how much space is being consumed by your S3 integration, you can run a command to list the files on your DB instance. For more information, see Integrating an Amazon RDS for SQL Server instance with Amazon S3.