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

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

A tempdb database is recreated each time an instance is restarted. To preventthe loss of user permissions, Cloud SQL provides permissions to thesqlserveruser after an instance is restarted.

Overview

Thesqlserveruserhas the ALTER permission for managing the tempdb database options.

For more information about managing this resource, see thetempdb databasepage.

Manage tempdb files

After youconnect to an instance, thesqlserveruser can manage the tempdb files.

Number of files

The user has ALTER permission on the tempdb database, which lets them controlsettings for the number of files and more. Some example operations include thefollowing:

• ALTER DATABASE [tempdb] ADD FILE
• ALTER DATABASE [tempdb] REMOVE

Note: SeePhysical properties of tempdb in SQL Server,which includes recommendations related to these settings. Review the informationabout the number of tempdb data files that would correspond to a given number oflogical processors. By default, Cloud SQL creates a minimum of eightfiles for instances that have greater than or equal to eight logical processors.As included in those recommendations, if the number of logical processorsis fewer than or equal to eight, the number of files created is equal to thenumber of logical processors.

File size

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

For more information about these methods, seeShrink the tempdb database.

Change file sizes in tempdb

To control the size of files in the tempdb database, use theALTER DATABASEstatement. For more information, seeALTER DATABASE (Transact-SQL) File and Filegroup Options.

Shrink individual file size

msdb.dbo.gcloudsql_tempdb_shrinkfile is astored procedureyou can use to shrink an individual file in thetempdb database.

This stored procedure provides all the same benefits of theDBCC SHRINKFILEcommand.

Caution: Avoid using shrinkfile operations as a part of regular maintenance;only use when necessary. For more information, seeDBCC SHRINKFILEdocumentation.

The following are example uses of themsdb.dbo.gcloudsql_tempdb_shrinkfilestored procedure and its parameters, executed from theCloud SQL Studio:

1. Default option

   msdb.dbo.gcloudsql_tempdb_shrinkfile@filename='FILENAME'

   Where:

   • FILENAME: 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

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

   Where:

   • EMPTY_FILE_INT: an integer value that is either 0 or 1. If 1,thenEMPTYFILE is passed as an option. This option takes priority overother parameters.

   This command executes the following SQL Server commands:

   • USE tempdb
   • DBCC SHRINKFILE (@filename, EMPTYFILE)

3. Target size

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

   Where:

   • TARGET_SIZE_INT: an integer that represents the target size ofthe file in megabytes. Passes to theDBCC SHRINKFILE command any valuethat is greater than or equal to zero. For example,10.

   This command executes the following SQL Server commands. The integer 10 isincluded as an example:

   • USE tempdb
   • DBCC SHRINKFILE (@filename, 10)

4. Target size and truncate only

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

   Where:

   • TRUNCATE_ONLY_INT: accepts an integer value of either 0 or 1.If set to 1, thenTRUNCATEONLY is passed as an option. Target size isignored ifTRUNCATEONLY is passed. This option takes priority overNOTRUNCATE.

   This command executes the following SQL Server commands:

   • USE tempdb
   • DBCC SHRINKFILE (@filename, 10, TRUNCATEONLY)

5. Target size and no truncate option

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

   Where:

   • NO_TRUNCATE_INT: accepts an integer value of either 0 or 1.If set to 1, thenNOTRUNCATE is passed as an option.

   This command executes the following SQL Server commands:

   • USE tempdb
   • DBCC SHRINKFILE (@filename, 10, NOTRUNCATE)

What's next

• Learn aboutInstance settings.
• Learn aboutMonitoring Cloud SQL instances.