# 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 your Microsoft SQL Server DB instance](USER_ConnectToMicrosoftSQLServerInstance.md).
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](https://msdn.microsoft.com/en-us/library/ms190768%28v=sql.120%29.aspx) 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](https://technet.microsoft.com/en-us/library/ms175527%28v=sql.120%29.aspx) in the Microsoft documentation.
The following example demonstrates setting the size to 100 GB and file growth to 10 percent.
```
1. 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.
```
1. 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\$1shrink\$1tempdbfile 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.
```
1. use tempdb;
2. GO
3.
4. select name, * from sys.sysfiles;
5. GO
```
The following example shrinks a `tempdb` database file named `test_file`, and requests a new size of `10` megabytes:
```
1. 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](USER_RebootInstance.md).
The following example demonstrates setting the `SIZE` property to 1024 MB.
```
1. 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](Appendix.SQLServer.CommonDBATasks.Agent.md#SQLServerAgent.Replicate).
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](Overview.DBInstance.Modifying.md).
+ 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](USER_RebootInstance.md).