Accessing the tempdb database on Microsoft SQL Server DB instances on Amazon RDS - Amazon Relational Database Service

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 in the Microsoft documentation.

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 in the Microsoft documentation.

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

@temp_filename

SYSNAME

required

The logical name of the file to shrink.

@target_size

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 modify tempdb in the new primary instance. This method involves downtime.

    For more information, see Rebooting a DB instance.