Ridurre le dimensioni del database tempdb

Si applica a:SQL Server Istanza gestita di Azure SQL

Questo articolo illustra vari metodi che è possibile utilizzare per compattare il tempdb database in SQL Server.

È possibile usare uno dei metodi seguenti per modificare le dimensioni di tempdb. Le prime tre opzioni sono descritte in questo articolo. Se vuoi usare SQL Server Management Studio (SSMS), segui le istruzioni in Compattare un database.

metodo	Richiede il riavvio?	Ulteriori informazioni
`ALTER DATABASE`	Sì	Fornisce il controllo completo sulle dimensioni dei file predefiniti `tempdb` (`tempdev` e `templog`).
`DBCC SHRINKDATABASE`	NO	Opera a livello del database.
`DBCC SHRINKFILE`	NO	Consente di compattare singoli file.
SQL Server Management Studio	NO	Compattare i file di database tramite un'interfaccia utente grafica.

Osservazioni:

Per impostazione predefinita, il database tempdb è configurato per l'aumento automatico in base alle esigenze. Pertanto, questo database potrebbe aumentare in modo imprevisto nel tempo fino a dimensioni superiori alle dimensioni desiderate. Le dimensioni maggiori del database tempdb non influiscono negativamente sulle prestazioni di SQL Server.

All'avvio di SQL Server, viene ricreato tempdb usando una copia del database model e reimposta tempdb all'ultima dimensione configurata. Le dimensioni configurate sono l'ultima dimensione esplicita che hai impostato utilizzando un'operazione di modifica della dimensione del file, ad esempio con l'opzione MODIFY FILE o le istruzioni DBCC SHRINKFILE o DBCC SHRINKDATABASE. Pertanto, a meno che non sia necessario usare valori diversi o si voglia risolvere immediatamente un database di grandi dimensioni tempdb , è possibile attendere il successivo riavvio del servizio SQL Server per ridurre le dimensioni.

È possibile ridurre tempdb mentre l'attività tempdb è in corso. Tuttavia, è possibile che si verifichino altri errori, ad esempio blocchi, stalli e altri ancora, che possono impedire il completamento del processo di riduzione. Per assicurarsi che una riduzione tempdb venga effettuata con successo, eseguire questa operazione mentre il server è in modalità utente singolo o quando si ferma tutta l'attività tempdb.

SQL Server registra solo informazioni sufficienti nel log delle transazioni tempdb per eseguire il rollback di una transazione, ma non per ripetere le transazioni durante il recupero del database. Questa funzione migliora le prestazioni delle dichiarazioni INSERT in tempdb. Inoltre, non è necessario registrare le informazioni per ripetere le transazioni perché tempdb viene ricreata ogni volta che si riavvia SQL Server. Di conseguenza, non ci sono transazioni da portare avanti o annullare.

Per altre informazioni sulla gestione e il monitoraggio di tempdb, vedere Pianificazione della capacità e Monitoraggio dell'uso di tempdb.

Usare il comando ALTER DATABASE

Nota

Questo comando funziona solo sui file predefiniti logici tempdb e tempdev e templog. Se si aggiungono altri file a tempdb, è possibile compattarli dopo il riavvio di SQL Server come servizio. Tutti i file tempdb vengono ricreati durante l'avvio. Tuttavia, questi file sono vuoti e possono essere rimossi. Per rimuovere file aggiuntivi in tempdb, usare il ALTER DATABASE comando con l'opzione REMOVE FILE .

Questo metodo richiede il riavvio di SQL Server.

Nota

È possibile connettersi a un'istanza di SQL Server usando qualsiasi strumento client di SQL Server familiare, ad esempio sqlcmd, SQL Server Management Studio (SSMS) o l'estensione MSSQL per Visual Studio Code.

Fermare SQL Server.
Al prompt dei comandi avviare l'istanza in modalità di configurazione minima. A tale scopo, effettuare i passaggi seguenti:
1. Al prompt dei comandi passare alla cartella in cui è installato SQL Server (sostituire <VersionNumber> e <InstanceName> nel seguente esempio):
```
cd C:\Program Files\Microsoft SQL Server\MSSQL<VersionNumber>.<InstanceName>\MSSQL\Binn
```
2. Se l'istanza è un'istanza denominata di SQL Server, eseguire il seguente comando (sostituire <InstanceName> nell'esempio seguente):
```
sqlservr.exe -s <InstanceName> -c -f -mSQLCMD
```
3. Se l'istanza è l'istanza predefinita di SQL Server, eseguire il seguente comando:
```
sqlservr -c -f -mSQLCMD
```
  Nota
  
  I parametri -c e -f determinano l'avvio di SQL Server in una modalità di configurazione minima con dimensioni di tempdb pari a 1 MB per il file di dati e 0,5 MB per il file di resoconto. Il parametro -mSQLCMD impedisce a qualsiasi applicazione diversa da sqlcmd di assumere la connessione a utente singolo.

Connettersi a SQL Server con sqlcmd, quindi eseguire i comandi Transact-SQL seguenti. Sostituire <target_size_in_MB> con le dimensioni desiderate:

ALTER DATABASE tempdb MODIFY FILE
(NAME = 'tempdev', SIZE = <target_size_in_MB>);

ALTER DATABASE tempdb MODIFY FILE
(NAME = 'templog', SIZE = <target_size_in_MB>);

Fermare SQL Server. A tale scopo, premere Ctrl+C nella finestra del prompt dei comandi, riavviare SQL Server as a Service e quindi controllare le dimensioni dei tempdb.mdf file e templog.ldf .

Usare il comando DBCC SHRINKDATABASE

DBCC SHRINKDATABASE accetta il target_percent parametro . Questo parametro imposta la percentuale di spazio disponibile che si desidera lasciare nel file di database dopo la compattazione del database. Se si usa DBCC SHRINKDATABASE, potrebbe essere necessario riavviare SQL Server.

Utilizzare la procedura memorizzata sp_spaceused per controllare lo spazio attualmente usato da tempdb. Calcolare quindi la percentuale di spazio disponibile da usare come parametro per DBCC SHRINKDATABASE. Questo calcolo si basa sulle dimensioni del database desiderate.

Nota

In alcuni casi, potrebbe essere necessario eseguire sp_spaceused @updateusage = true per ricalcolare lo spazio usato e ottenere un report aggiornato. Per altre informazioni, consultare ssp_spaceused.

Si consideri l'esempio seguente:

Si supponga che tempdb abbia due file: il file di dati primario (tempdb.mdf) di 1024 MB e il file di resoconto (tempdb.ldf) pari a 360 MB. Si supponga che sp_spaceused segnali che il file di dati primario contenga 600 MB di dati. Inoltre, si supponga di voler compattare il file di dati primario a 800 MB. Calcolare la percentuale desiderata di spazio disponibile lasciato dopo la compattazione: 800 MB - 600 MB = 200 MB. Dividi ora 200 MB per 800 MB = 25% e tale valore è il tuo target_percent. Il file registro transazioni viene ridotto di conseguenza, lasciando disponibile il 25% o 200 MB di spazio dopo che il database è stato compattato.
Eseguire il comando Transact-SQL seguente. Sostituire <target_percent> con la percentuale desiderata:
```
DBCC SHRINKDATABASE (tempdb, '<target_percent>');
```

Il DBCC SHRINKDATABASE comando presenta limitazioni quando viene usato in tempdb. Non è possibile impostare le dimensioni di destinazione per i file di dati e di log in modo che siano inferiori alle dimensioni specificate al momento della creazione del database. Non è inoltre possibile impostarla più piccola dell'ultima dimensione impostata in modo esplicito usando un'operazione di modifica delle dimensioni del file, ALTER DATABASE ad esempio con l'opzione MODIFY FILE . Un'altra limitazione di DBCC SHRINKDATABASE è il calcolo del target_percentage parametro e la relativa dipendenza dallo spazio corrente usato.

Usare il comando DBCC SHRINKFILE

Usare il DBCC SHRINKFILE comando per compattare singoli tempdb file. DBCC SHRINKFILE offre maggiore flessibilità rispetto DBCC SHRINKDATABASE in quanto è possibile usarlo in un singolo file di database senza influire sugli altri file appartenenti allo stesso database. DBCC SHRINKFILE accetta il target_size parametro . Questo parametro imposta le dimensioni finali desiderate per il file di database.

Determinare le dimensioni desiderate per il file di dati primario (tempdb.mdf), il file di resoconto (templog.ldf) e i file extra aggiunti a tempdb. Assicurarsi che lo spazio usato nei file sia minore o uguale alla dimensione di destinazione desiderata.

Connettersi a SQL Server con SSMS, Visual Studio Code o sqlcmd. Eseguire quindi i comandi di Transact-SQL seguenti per i file di database specifici da compattare. Sostituire <target_size_in_MB> con le dimensioni desiderate:

USE tempdb;
GO

-- This command shrinks the primary data file
DBCC SHRINKFILE (tempdev, '<target_size_in_MB>');
GO

-- This command shrinks the log file, examine the last paragraph.
DBCC SHRINKFILE (templog, '<target_size_in_MB>');
GO

Un vantaggio di DBCC SHRINKFILE è che può ridurre le dimensioni di un file fino a essere più piccole rispetto a quelle originali. È possibile eseguire DBCC SHRINKFILE in uno qualsiasi dei file di dati o di log. Non è possibile rendere il database inferiore alle dimensioni del database model.

Errore 8909 quando si eseguono operazioni di compattazione

Se tempdb è in uso e si tenta di compattarlo usando i DBCC SHRINKDATABASE comandi o DBCC SHRINKFILE , è possibile ricevere messaggi simili all'output seguente. Il messaggio esatto dipende dalla versione di SQL Server in uso:

Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (6:8040) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).

Questo errore non indica alcun danneggiamento reale in tempdb. Tuttavia, potrebbero esserci altri motivi per errori di danneggiamento dei dati fisici, ad esempio l'errore 8909. Anche questi motivi includono problemi del sottosistema I/O. Pertanto, se l'errore si verifica al di fuori delle operazioni di compattazione, è necessario esaminare ulteriormente.

Anche se viene restituito un messaggio 8909 all'applicazione o all'utente che sta eseguendo l'operazione di compattazione, le operazioni di compattazione non falliscono.

Commenti e suggerimenti

Questa pagina è stata utile?

Last updated on 2026-01-27