Accessing the tempdb database on Microsoft SQL Server DB instances on Amazon RDS
You can access the tempdb
database on your Microsoft SQL Server DB instances on
Amazon RDS. You can run code on tempdb
by using Transact-SQL through Microsoft SQL
Server Management Studio (SSMS), or any other standard SQL client application. For more
information about connecting to your DB instance, see Connecting to a DB instance running the Microsoft SQL Server database engine.
The master user for your DB instance is granted CONTROL
access to
tempdb
so that this user can modify the tempdb
database
options. The master user isn't the database owner of the tempdb
database. If
necessary, the master user can grant CONTROL
access to other users so that they
can also modify the tempdb
database options.
Note
You can't run Database Console Commands (DBCC) on the tempdb
database.
Modifying tempdb database options
You can modify the database options on the tempdb
database on your Amazon RDS DB
instances. For more information about which options can be modified, see tempdb
database
Database options such as the maximum file size options are persistent after you restart your DB instance. You can modify the database options to optimize performance when importing data, and to prevent running out of storage.
Optimizing performance when importing data
To optimize performance when importing large amounts of data into your DB instance, set the
SIZE
and FILEGROWTH
properties of the tempdb database
to large numbers. For more information about how to optimize tempdb
,
see Optimizing tempdb performance
The following example demonstrates setting the size to 100 GB and file growth to 10 percent.
alter database[tempdb] modify file (NAME = N'
templog
', SIZE=100GB
, FILEGROWTH =10%
)
Preventing storage problems
To prevent the tempdb
database from using all available disk space, set the
MAXSIZE
property. The following example demonstrates setting the
property to 2048 MB.
alter database [tempdb] modify file (NAME = N'
templog
', MAXSIZE =2048MB
)
Shrinking the tempdb database
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,
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
. When you call rds_shrink_tempdbfile
, there is
no downtime for your DB instance.
The rds_shrink_tempdbfile
procedure has the following parameters.
Parameter name | Data type | Default | Required | Description |
---|---|---|---|---|
|
SYSNAME |
— |
required |
The logical name of the file to shrink. |
|
int |
null |
optional |
The new size for the file, in megabytes. |
The following example gets the names of the files for the tempdb
database.
use tempdb; GO select name, * from sys.sysfiles; GO
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
;
Setting the SIZE property
You can also shrink the tempdb
database by setting the SIZE
property and then restarting your DB instance. For more information about restarting
your DB instance, see Rebooting a DB instance.
The following example demonstrates setting the SIZE
property to 1024
MB.
alter database [tempdb] modify file (NAME = N'
templog
', SIZE =1024MB
)
TempDB configuration for Multi-AZ deployments
If your RDS for SQL Server DB instance is in a Multi-AZ Deployment using Database Mirroring (DBM) or Always On Availability Groups (AGs), keep in mind the following
considerations for using the tempdb
database.
You can't replicate tempdb
data from your primary DB instance to your secondary DB instance. When you fail over to
a secondary DB instance, tempdb
on that secondary DB instance will be empty.
You can synchronize the configuration of the tempdb
database options, including its file sizing and autogrowth settings,
from your primary DB instance to your secondary DB instance. Synchronizing the tempDB
configuration is supported on all RDS for SQL Server versions.
You can turn on automatic synchronization of the tempdb
configuration by using the following stored procedure:
EXECUTE msdb.dbo.rds_set_system_database_sync_objects @object_types = 'TempDbFile';
Important
Before using the rds_set_system_database_sync_objects
stored procedure, ensure you've set your preferred tempdb
configuration
on your primary DB instance, rather than on your secondary DB instance. If you made the configuration change on your secondary DB instance, your preferred
tempdb
configuration could be deleted when you turn on automatic synchronization.
You can use the following function to confirm whether automatic synchronization of the tempdb
configuration is turned on:
SELECT * from msdb.dbo.rds_fn_get_system_database_sync_objects();
When automatic synchronization of the tempdb
configuration is turned on, there will be a return value for the object_class
field. When
it's turned off, no value is returned.
You can use the following function to find the last time objects were synchronized, in UTC time:
SELECT * from msdb.dbo.rds_fn_server_object_last_sync_time();
For example, if you modified the tempdb
configuration at 01:00 and then run the rds_fn_server_object_last_sync_time
function,
the value returned for last_sync_time
should be after 01:00, indicating that an automatic synchronization occurred.
If you are also using SQL Server Agent job replication, you can enable replication for both SQL Agent jobs and the tempdb
configuration by providing them in the @object_type
parameter:
EXECUTE msdb.dbo.rds_set_system_database_sync_objects @object_types = 'SQLAgentJob,TempDbFile';
For more information on SQL Server Agent job replication, see Turning on SQL Server Agent job replication.
As an alternative to using the rds_set_system_database_sync_objects
stored procedure to ensure that tempdb
configuration changes
are automatically synchronized, you can use one of the following manual methods:
Note
We recommend turning on automatic synchronization of the tempdb
configuration by using the rds_set_system_database_sync_objects
stored procedure.
Using automatic synchronization prevents the need to perform these manual tasks each time you change your tempdb
configuration.
-
First modify your DB instance and turn Multi-AZ off, then modify tempdb, and finally turn Multi-AZ back on. This method doesn't involve any downtime.
For more information, see Modifying an Amazon RDS DB instance.
-
First modify
tempdb
in the original primary instance, then fail over manually, and finally modifytempdb
in the new primary instance. This method involves downtime.For more information, see Rebooting a DB instance.