RDS/SQL Server Instance Storage Continues to Fill Even After Shrinking tempdb

Question

Greetings,

This RDS instance has 960db of storage allocated. The only client database currently running is pre-sized to 400gb with 68% free. The tempdb grew to 400gb, however, we were able to shrink those down to 8GB. We have set all 5 traces to one day retention, and there is no auditing turned on. tempdb is not growing fast at all. With all that, the message below continues to be sent.

> Message : Storage size 960 GB is approaching the maximum storage threshold 960 GB. Increase the maximum storage threshold.

It appeasers that by default, RDS retains 7 days of backup, however, it is not clear how those backups are stored. Is it the backups that are causing storage issues?

Also, is there any documentation or is it possible to actually see what is being stored in storage? The database files can be queried but what else is being stored out there? The assumption was that 960gb would be sufficient for a database that would eventually reach 600gb.

kranthi putti · Accepted Answer

Hello,

The issue is likely due to the 7-day backup retention period, which is the default for RDS. Even though you've shrunk the tempdb, the backup files are still consuming storage space.

Reduce the backup retention period: Lower the retention period to a minimum of 1 day to reduce the storage consumed by backups. You can do this in the RDS dashboard, under "Instance settings" > "Backup & maintenance".

**Verify backup storage usage: Use the AWS CLI command:**

```
aws rds describe-db-instances --db-instance-identifier  --query 'DBInstances[0].BackupStorageUsed'
```
to check the backup storage usage.

**Check for other storage consumers: Run the query:**

```
SELECT * FROM sys.dm_db_file_space_usage

```
to identify any other files or objects consuming storage space.
Monitor storage usage: Regularly check the storage usage in the RDS dashboard to ensure it's within the expected range.

AWS RDS Backup and Restore: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithAutomatedBackups.html

AWS RDS Storage: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Storage.html

RDS/SQL Server Instance Storage Continues to Fill Even After Shrinking tempdb

Relevant content