My Amazon RDS database instance running on the SQL Server engine uses 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?
Although it is possible to use the Amazon CloudWatch metric ‘FreeStorageSpace’ to monitor available storage space for a given RDS DB instance, FreeStorageSpace does not describe how the SQL Server engine is consuming available storage.
Note
To gather more detailed information about the actual physical disk space usage for a SQL Server database instance, run the following query:
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 contain data, files containing LOGS represent in-flight transactions.
Before optimizing the use of available storage, it is helpful to know 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 (e.g., USE [my_database];) with the following query:
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; if the transaction log has an unusually large amount of free space, deallocate the excessive free space by using the following query:
USE [mydatabasename];
DECLARE @minmb int;
DECLARE @logfile nvarchar(255);
SET @minmb = 5;
SET @logfile = (SELECT name FROM sys.master_files WHERE type = 1 AND database_id = db_id());
DBCC SHRINKFILE(@logfile, @minmb);
To reduce excessive allocation of transaction log free space, use ALTER DATABASE File and Filegroup Options (Transact-SQL) to configure auto growth settings appropriately for the user database in question. - Temporary database (tempdb)
The SQL Server tempdb grows automatically until it is recreated when the DB instance is restarted. If the tempdb is consuming a large amount of available storage, you should define a maintenance window during which you can stop and restart the DB instance. - Database indexes
When a significant portion of available storage is dedicated to indexes, you may be able to conserve some space through index tuning. While employing different index tuning strategies, you can gather detailed information about index usage by running the sys.dm_db_index_usage_stats (Transact-SQL) dynamic management view. This information can be helpful for evaluating tuning priorities, such as whether the benefit of dropping an index to conserve storage would offset the performance cost of the missing index.
Amazon RDS, storage, DBCC SHRINKFILE, FreeStorageSpace, excessive storage space, tempdb, transaction log, index tuning
Microsoft SQL Server on Amazon RDS
Analyzing Your Database Workload on a DB Instance Using SQL Server Tuning Advisor
Using SQL Server Agent
Viewing DB Instance Metrics
Monitoring Amazon RDS
Amazon RDS DB Instance Running Out of Storage
Modifying a DB Instance and Using the Apply Immediately Parameter
Did this page help you? Yes | No
Back to the AWS Support Knowledge Center
Need help? Visit the AWS Support Center.