How can I troubleshoot storage consumption in my RDS for SQL Server DB instance?

Last updated: 2022-10-17

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

Short description

You can monitor available storage space for a DB instance using the FreeStorageSpace metric in Amazon CloudWatch. Frequently monitoring this metric and turning on storage auto-scaling helps prevent instances from running out of storage (Storage Full state).

However, the FreeStorageSpace metric doesn't describe how the SQL Server engine is consuming available storage.

Resolution

Amazon RDS for SQL Server instances in the Storage Full state

You can't perform basic operations when your RDS instance is stuck in the Storage Full state. For more information, see How do I resolve problems that occur when Amazon RDS DB instances run out of storage?

Some RDS for SQL Server DB instances have limitations for modifying storage. In the Amazon RDS console, the Allocated storage option is deactivated if your DB instance isn’t eligible to be modified. To scale storage on an instance when the modify option isn't available, migrate your data using native backup and restore to a new instance. Make sure that the new instance has Provisioned IOPS or has the General Purpose (SSD) storage type. Or, use a data migration tool to migrate to the new instance. For more information, see Modifying an Amazon RDS DB instance.

Use the following AWS Command Line Interface (AWS CLI) command to return the valid storage options for your DB instance:

describe-valid-db-instance-modifications

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

For instances that have storage autoscaling turned on, storage is extended only in certain scenarios. For more information, see Managing capacity automatically with Amazon RDS storage autoscaling. In addition, storage is extended only if the maximum storage threshold doesn't equal or exceed the storage increment. For more information, see Limitations.

Storage consumption for RDS for SQL Server instances

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. To do this, run 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 the ALTER DATABASE (transact-SQL) file and filegroup options. The options 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, then see the Microsoft documentation for DBCC FREESYSTEMCACHE and DBCC FREEPROCCACHE. You can also reboot the DB instance to clear the tempdb.

DB instance's in a Storage Full status might not be able to  reboot. If this occurs, 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're dedicating a significant portion of your available storage to indexes, then 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 Setting the retention period for trace and dump files.

Space consumed by Amazon S3 integration

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

CDC

For databased that have CDC turned on, log file size increases depending on the frequency of changes to the source tables or databases. Storage might eventually run out. If the log disk becomes full, then CDC can't process further transactions.

Auditing

If auditing isn't configured correctly for instance, the logs might grow exponentially and affect storage. For more information, see Using 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 instance into the Storage Full state. For more information, see C2 audit mode server configuration option in the Microsoft documentation.

In addition, turning on features such as query store might also impact resource utilization.