How to set the Initial File Size of tempdb on an RDS/SQL Server Instance

In order to manage the TEMPDB size, you can shrink the database and further set a MAXSIZE to restrict its growth.

---

Firstly, let's review how to SHRINK the TEMPDB size:

There are two ways to shrink the tempdb database on your Amazon RDS DB instance. You can use the rds_shrink_tempdbfile procedure, or you can set the SIZE property,

1. Using the rds_shrink_tempdbfile procedure: You can use the Amazon RDS procedure msdb.dbo.rds_shrink_tempdbfile to shrink the tempdb database. You can only call rds_shrink_tempdbfile if you have CONTROL access to TEMPDB.

NOTE: When you call rds_shrink_tempdbfile, there is no downtime for your DB instance.

The following example shrinks a tempdb database file named test_file, and requests a new size of 10 megabytes:

exec msdb.dbo.rds_shrink_tempdbfile @temp_filename = N'test_file', @target_size = 10;

For more information, please refer to document, Using the rds_shrink_tempdbfile procedure

2. Setting the SIZE property: You can also shrink the tempdb database by setting the SIZE property and then restarting your DB instance.

The following example demonstrates setting the SIZE property to 1024 MB:

alter database [tempdb] modify file (NAME = N'templog', SIZE = 1024MB)

---

Next, let's review how to prevent the TEMPDB database from using all available disk space, by setting the MAXSIZE property:

The following example demonstrates setting the property to 2048 MB:

alter database [tempdb] modify file (NAME = N'templog', MAXSIZE = 2048MB)

NOTE: Setting the MAXSIZE property does not require downtime.

---

Reference:

Please refer to the document below for detailed information on how to manage TEMPDB

[+] Accessing the tempdb database on Microsoft SQL Server DB instances on Amazon RDS - https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.TempDB.html