Managing a tempdb database

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 has the ALTER permission for managing the tempdb database options.

For more information about managing this resource, see the tempdb database page.

Managing tempdb files

After you connect to an instance, the sqlserver user 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. Some example operations include the following:

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

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.

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.

Shrink individual file size

msdb.dbo.gcloudsql_tempdb_shrinkfile is a stored procedure you can use to shrink an individual file in the tempdb database.

This stored procedure provides all the same benefits of the DBCC SHRINKFILE command.

The following are example uses of the msdb.dbo.gcloudsql_tempdb_shrinkfile stored procedure and its parameters, executed from the Cloud 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, 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

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

    Where:

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

    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, 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

    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, 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