Help us improve the AWS re:Post Knowledge Center by sharing your feedback in a brief survey. Your input can influence how we create and update our content to better support your AWS journey.
How to Free Disk Space in RDS/SQL Server
Greetings,
A RDS SQL Server instance is provisioned with 960 gb of disk space. Alerts started being sent about disk space, however, the only client database on that instance is only 400GB.
Running the query below:
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;
Produced these results:
tempdev T:\rdsdbdata\DATA\tempdb.mdf 114083.69
templog T:\rdsdbdata\DATA\templog.ldf 136.00
temp2 T:\rdsdbdata\DATA\tempdb_mssql_2.ndf 114390.00
temp3 T:\rdsdbdata\DATA\tempdb_mssql_3.ndf 114390.00
temp4 T:\rdsdbdata\DATA\tempdb_mssql_4.ndf 114390.00
Following recommendations, I ran:
EXEC msdb.dbo.rds_shrink_tempdbfile 'tempdev' ,10
EXEC msdb.dbo.rds_shrink_tempdbfile 'temp2' ,10
EXEC msdb.dbo.rds_shrink_tempdbfile 'temp3' ,10
EXEC msdb.dbo.rds_shrink_tempdbfile 'temp4' ,10
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
2 1 14602712 14602712 400 400
It is telling me that the MIn Size = Current size, thusly, the file can't be shrunk. The SSMS console does not allow me to shrink.
Questions:
- How can I get tempdb back down to say 4 gb?
- Language
- English
- Newest
- Most votes
- Most comments
Monitor the Growth:
After the instance restarts, monitor the growth of tempdb to ensure that it doesn't grow excessively again. You might need to review and optimize the queries and processes that are used.
and here is a complete T-SQL script to modify the tempdb file sizes:
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, SIZE = 4096MB);
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = temp2, SIZE = 4096MB);
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = temp3, SIZE = 4096MB);
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = temp4, SIZE = 4096MB);
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, SIZE = 1024MB);
GO
Thanks, I was trying to set the size another way and that was not working. This approach works.
Relevant content
- asked 3 years ago
- asked 3 years ago
- asked 4 years ago
By adjusting the initial sizes of the tempdb files and rebooting the instance, you should be able to reduce the size of tempdb and manage disk space usage more effectively. Regular monitoring and query optimization can help prevent tempdb from growing excessively in the future.