Mantieni tutto organizzato con le raccolte
Salva e classifica i contenuti in base alle tue preferenze.
MySQL | PostgreSQL | SQL Server
Questa pagina descrive la gestione del database tempdb in Cloud SQL.
Il database tempdb è un database di sistema che contiene molti oggetti, tra cui tabelle temporanee, stored procedure e altro ancora. Nelle tue istanze, puoi
eseguire operazioni comuni su questo database.
Un database tempdb viene ricreato ogni volta che viene riavviata un'istanza. Per evitare
la perdita delle autorizzazioni utente, Cloud SQL fornisce le autorizzazioni all'utente sqlserver
dopo il riavvio di un'istanza.
Panoramica
L'sqlserverutente
dispone dell'autorizzazione ALTER per la gestione delle opzioni del database tempdb.
Per ulteriori informazioni sulla gestione di questa risorsa, consulta la pagina Database tempdb.
L'utente dispone dell'autorizzazione ALTER sul database tempdb, che gli consente di controllare
le impostazioni per il numero di file e altro ancora. Alcune operazioni di esempio includono
le seguenti:
ALTER DATABASE [tempdb] ADD FILE
ALTER DATABASE [tempdb] REMOVE
Dimensioni file
Le sezioni seguenti descrivono i metodi utilizzati per controllare le dimensioni dei file nel database tempdb.
msdb.dbo.gcloudsql_tempdb_shrinkfile è una stored procedure
che puoi utilizzare per ridurre le dimensioni di un singolo file nel database tempdb.
Questa stored procedure offre gli stessi vantaggi del comando
DBCC SHRINKFILE.
.
Di seguito sono riportati esempi di utilizzo della stored procedure msdb.dbo.gcloudsql_tempdb_shrinkfile e dei relativi parametri, eseguiti da Cloud SQL Studio:
TARGET_SIZE_INT: un numero intero che rappresenta la dimensione target del file in megabyte. Passa al comando DBCC SHRINKFILE qualsiasi valore
maggiore o uguale a zero. Ad esempio: 10.
Questo comando esegue i seguenti comandi SQL Server. L'intero 10 è
incluso come esempio:
TRUNCATE_ONLY_INT: accetta un valore intero pari a 0 o 1.
Se è impostato su 1, TRUNCATEONLY viene passato come opzione. La dimensione target viene
ignorata se viene passato TRUNCATEONLY. Questa opzione ha la priorità su
NOTRUNCATE.
Questo comando esegue i seguenti comandi SQL Server:
USE tempdb
DBCC SHRINKFILE (@filename, 10, TRUNCATEONLY)
Dimensioni target e nessuna opzione di troncamento
[[["Facile da capire","easyToUnderstand","thumb-up"],["Il problema è stato risolto","solvedMyProblem","thumb-up"],["Altra","otherUp","thumb-up"]],[["Difficile da capire","hardToUnderstand","thumb-down"],["Informazioni o codice di esempio errati","incorrectInformationOrSampleCode","thumb-down"],["Mancano le informazioni o gli esempi di cui ho bisogno","missingTheInformationSamplesINeed","thumb-down"],["Problema di traduzione","translationIssue","thumb-down"],["Altra","otherDown","thumb-down"]],["Ultimo aggiornamento 2025-09-04 UTC."],[],[],null,["# Manage a tempdb database\n\n\u003cbr /\u003e\n\nMySQL \\| PostgreSQL \\| SQL Server\n\n\u003cbr /\u003e\n\nThis page describes management of the tempdb database in Cloud SQL.\n\nThe tempdb database is a system database that holds many objects, including\ntemporary tables, stored procedures, and more. In your instances, you can\nperform common operations on this database.\n\nA tempdb database is recreated each time an instance is restarted. To prevent\nthe loss of user permissions, Cloud SQL provides permissions to the `sqlserver`\nuser after an instance is restarted.\n\nOverview\n--------\n\nThe `sqlserver` [user](/sql/docs/sqlserver/users)\nhas the ALTER permission for managing the tempdb database options.\n\nFor more information about managing this resource, see the [tempdb database](https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database)\npage.\n\nManage tempdb files\n-------------------\n\nAfter you [connect](/sql/docs/sqlserver/connect-overview) to an instance, the\n`sqlserver` [user](/sql/docs/sqlserver/users) can manage the tempdb files.\n\nNumber of files\n---------------\n\nThe user has ALTER permission on the tempdb database, which lets them control\nsettings for the number of files and more. Some example operations include the\nfollowing:\n\n- `ALTER DATABASE [tempdb] ADD FILE`\n- `ALTER DATABASE [tempdb] REMOVE`\n\n| **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.\n\nFile size\n---------\n\nThe following sections describe methods used to control the size of files in the\n`tempdb` database.\n\nFor more information about these methods, see\n[Shrink the tempdb database](https://learn.microsoft.com/en-us/sql/relational-databases/databases/shrink-tempdb-database?view=sql-server-ver16).\n\n### Change file sizes in tempdb\n\nTo control the size of files in the tempdb database, use the `ALTER DATABASE`\nstatement. For more information, see\n[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).\n\n### Shrink individual file size\n\n`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)\nyou can use to shrink an individual file in the `tempdb` database.\n\nThis stored procedure provides all the same benefits of the\n[`DBCC SHRINKFILE`](https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-ver16)\ncommand.\n| **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.\n\nThe following are example uses of the `msdb.dbo.gcloudsql_tempdb_shrinkfile`\nstored procedure and its parameters, executed from the [Cloud SQL Studio](/sql/docs/sqlserver/manage-data-using-studio):\n\n1. Default option\n\n ```sql\n msdb.dbo.gcloudsql_tempdb_shrinkfile @filename = '\u003cvar translate=\"no\"\u003eFILENAME\u003c/var\u003e'\n ```\n\n Where:\n - \u003cvar translate=\"no\"\u003eFILENAME\u003c/var\u003e: the name of the file to be shrunk. For example, `tempdev`.\n\n This command executes the following SQL Server commands:\n - `USE tempdb`\n - `DBCC SHRINKFILE (@filename)`\n2. `EMPTYFILE`\n\n ```sql\n msdb.dbo.gcloudsql_tempdb_shrinkfile @filename = '\u003cvar translate=\"no\"\u003eFILENAME\u003c/var\u003e', @empty_file=EMPTY_FILE_INT\n ```\n\n Where:\n - \u003cvar translate=\"no\"\u003eEMPTY_FILE_INT\u003c/var\u003e: 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.\n\n This command executes the following SQL Server commands:\n - `USE tempdb`\n - `DBCC SHRINKFILE (@filename, EMPTYFILE)`\n3. Target size\n\n ```sql\n msdb.dbo.gcloudsql_tempdb_shrinkfile @filename = '\u003cvar translate=\"no\"\u003eFILENAME\u003c/var\u003e', @target_size=TARGET_SIZE_INT\n ```\n\n Where:\n - \u003cvar translate=\"no\"\u003eTARGET_SIZE_INT\u003c/var\u003e: 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`.\n\n This command executes the following SQL Server commands. The integer 10 is\n included as an example:\n - `USE tempdb`\n - `DBCC SHRINKFILE (@filename, 10)`\n4. Target size and truncate only\n\n ```sql\n msdb.dbo.gcloudsql_tempdb_shrinkfile @filename = '\u003cvar translate=\"no\"\u003eFILENAME\u003c/var\u003e', @target_size=10, @truncateonly=TRUNCATE_ONLY_INT\n ```\n\n Where:\n - \u003cvar translate=\"no\"\u003eTRUNCATE_ONLY_INT\u003c/var\u003e: 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`.\n\n This command executes the following SQL Server commands:\n - `USE tempdb`\n - `DBCC SHRINKFILE (@filename, 10, TRUNCATEONLY)`\n5. Target size and no truncate option\n\n ```sql\n msdb.dbo.gcloudsql_tempdb_shrinkfile @filename = '\u003cvar translate=\"no\"\u003eFILENAME\u003c/var\u003e', @target_size=10, @no_truncate=NO_TRUNCATE_INT\n ```\n\n Where:\n - \u003cvar translate=\"no\"\u003eNO_TRUNCATE_INT\u003c/var\u003e: accepts an integer value of either 0 or 1. If set to 1, then `NOTRUNCATE` is passed as an option.\n\n This command executes the following SQL Server commands:\n - `USE tempdb`\n - `DBCC SHRINKFILE (@filename, 10, NOTRUNCATE)`\n\nWhat's next\n-----------\n\n- Learn about [Instance settings](/sql/docs/sqlserver/instance-settings).\n- Learn about [Monitoring Cloud SQL instances](/sql/docs/sqlserver/monitor-instance)."]]
