This page describes management of the tempdb database in Cloud SQL.

The tempdb database is a system database that holds many objects, including
temporary tables, stored procedures, and more. In your instances, you can
perform common operations on this database.

A tempdb database is recreated each time an instance is restarted. To prevent
the loss of user permissions, Cloud SQL provides permissions to the `sqlserver`
user after an instance is restarted.

## Overview

The `sqlserver` [user](https://docs.cloud.google.com/sql/docs/sqlserver/users.md.txt)
has the ALTER permission for managing the tempdb database options.

For more information about managing this resource, see the [tempdb database](https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database)
page.

## Manage tempdb files

After you [connect](https://docs.cloud.google.com/sql/docs/sqlserver/connect-overview) to an instance, the
`sqlserver` [user](https://docs.cloud.google.com/sql/docs/sqlserver/users.md.txt) can manage the tempdb files.

## Number of files

The user has ALTER permission on the tempdb database, which lets them control
settings for the number of files and more.

When adding files, you must use the `/var/opt/mssql/data/` directory for the physical path.

Some example operations include the following:

- Add a file:

  ```sql
  ALTER DATABASE [tempdb] ADD FILE (NAME = LOGICAL_FILENAME, FILENAME = PHYSICAL_FILENAME, SIZE = SIZE)
  ```

  Make the following replacements:
  - `LOGICAL_FILENAME`: the logical name of the file. For example, `tempdev4`.
  - `PHYSICAL_FILENAME`: the complete path to the file. For example, `/var/opt/mssql/data/tempdb4.ndf`.
  - `SIZE`: the size of the file. For example, `1GB`.
- Remove a file:

  ```sql
  ALTER DATABASE [tempdb] REMOVE FILE LOGICAL_FILENAME
  ```

  Make the following replacement:
  - `LOGICAL_FILENAME`: the logical name of the file. For example, `tempdev4`.

> [!NOTE]
> **Note:** See [Physical properties of tempdb in SQL Server](https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database?view=sql-server-ver15#physical-properties-of-tempdb-in-sql-server), which includes recommendations related to these settings. Review the information about the number of tempdb data files that would correspond to a given number of logical processors. By default, Cloud SQL creates a minimum of eight files for instances that have greater than or equal to eight logical processors. As included in those recommendations, if the number of logical processors is fewer than or equal to eight, the number of files created is equal to the number of logical processors.

## File size

The following sections describe methods used to control the size of files in the
`tempdb` database.

For more information about these methods, see
[Shrink the tempdb database](https://learn.microsoft.com/en-us/sql/relational-databases/databases/shrink-tempdb-database?view=sql-server-ver16).

### Change file sizes in tempdb

To control the size of files in the tempdb database, use the `ALTER DATABASE`
statement. For more information, see
[ALTER DATABASE (Transact-SQL) File and Filegroup Options](https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-file-and-filegroup-options?redirectedfrom=MSDN&view=sql-server-ver15).

### Shrink individual file size

`msdb.dbo.gcloudsql_tempdb_shrinkfile` is a [stored procedure](https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/stored-procedures-database-engine?view=sql-server-ver16)
you can use to shrink an individual file in the `tempdb` database.

This stored procedure provides all the same benefits of the
[`DBCC SHRINKFILE`](https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-ver16)
command.

> [!CAUTION]
> **Caution:** Avoid using shrinkfile operations as a part of regular maintenance; only use when necessary. For more information, see [`DBCC SHRINKFILE`](https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-ver16) documentation.

The following are example uses of the `msdb.dbo.gcloudsql_tempdb_shrinkfile`
stored procedure and its parameters, executed from the [Cloud SQL Studio](https://docs.cloud.google.com/sql/docs/sqlserver/manage-data-using-studio.md.txt):

1. Default option

   ```sql
   msdb.dbo.gcloudsql_tempdb_shrinkfile @filename = 'FILENAME'
   ```

   Where:
   - <var translate="no">FILENAME</var>: the name of the file to be shrunk. For example, `tempdev`.

   This command executes the following SQL Server commands:
   - `USE tempdb`
   - `DBCC SHRINKFILE (@filename)`
2. `EMPTYFILE`

   ```sql
   msdb.dbo.gcloudsql_tempdb_shrinkfile @filename = 'FILENAME', @empty_file=EMPTY_FILE_INT
   ```

   Where:
   - <var translate="no">EMPTY_FILE_INT</var>: an integer value that is either 0 or 1. If 1, then `EMPTYFILE` is passed as an option. This option takes priority over other parameters.

   This command executes the following SQL Server commands:
   - `USE tempdb`
   - `DBCC SHRINKFILE (@filename, EMPTYFILE)`
3. Target size

   ```sql
   msdb.dbo.gcloudsql_tempdb_shrinkfile @filename = 'FILENAME', @target_size=TARGET_SIZE_INT
   ```

   Where:
   - <var translate="no">TARGET_SIZE_INT</var>: an integer that represents the target size of the file in megabytes. Passes to the `DBCC SHRINKFILE` command any value that is greater than or equal to zero. For example, `10`.

   This command executes the following SQL Server commands. The integer 10 is
   included as an example:
   - `USE tempdb`
   - `DBCC SHRINKFILE (@filename, 10)`
4. Target size and truncate only

   ```sql
   msdb.dbo.gcloudsql_tempdb_shrinkfile @filename = 'FILENAME', @target_size=10, @truncateonly=TRUNCATE_ONLY_INT
   ```

   Where:
   - <var translate="no">TRUNCATE_ONLY_INT</var>: accepts an integer value of either 0 or 1. If set to 1, then `TRUNCATEONLY` is passed as an option. Target size is ignored if `TRUNCATEONLY` is passed. This option takes priority over `NOTRUNCATE`.

   This command executes the following SQL Server commands:
   - `USE tempdb`
   - `DBCC SHRINKFILE (@filename, 10, TRUNCATEONLY)`
5. Target size and no truncate option

   ```sql
   msdb.dbo.gcloudsql_tempdb_shrinkfile @filename = 'FILENAME', @target_size=10, @no_truncate=NO_TRUNCATE_INT
   ```

   Where:
   - <var translate="no">NO_TRUNCATE_INT</var>: accepts an integer value of either 0 or 1. If set to 1, then `NOTRUNCATE` is passed as an option.

   This command executes the following SQL Server commands:
   - `USE tempdb`
   - `DBCC SHRINKFILE (@filename, 10, NOTRUNCATE)`

## What's next

- Learn about [Instance settings](https://docs.cloud.google.com/sql/docs/sqlserver/instance-settings.md.txt).
- Learn about [Monitoring Cloud SQL instances](https://docs.cloud.google.com/sql/docs/sqlserver/monitor-instance.md.txt).