Condividi tramite

Compattare il database tempdb

  • Articolo

Si applica a: SQL Server e Istanza gestita di SQL di Azure

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 si vuole usare SQL Server Management Studio (SSMS), seguire 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, col tempo questo database può aumentare in modo imprevisto fino a raggiungere una dimensione maggiore di quella desiderata. Le dimensioni maggiori del database tempdb non influiscono negativamente sulle prestazioni di SQL Server.

All'avvio di SQL Server, tempdb viene ricreato usando una copia del database model e tempdb viene ripristinato all'ultima dimensione configurata. Le dimensioni configurate sono le ultime dimensioni esplicite impostate usando un'operazione di modifica delle dimensioni del file, ad esempio ALTER DATABASE che usa l'opzione MODIFY FILE o le istruzioni DBCC SHRINKFILE o DBCC SHRINKDATABASE. Pertanto, a meno che non sia necessario usare valori diversi o ottenere una risoluzione immediata in un database tempdb di grandi dimensioni, è possibile attendere il successivo riavvio del servizio SQL Server per ridurre le dimensioni.

È possibile compattare tempdb mentre l'attività tempdb è in corso. Tuttavia, è possibile che si verifichino altri errori, ad esempio bloccaggio, deadlock e altri ancora, che possono impedire il completamento della compattazione. Pertanto, per assicurarsi che una compattazione di tempdb abbia esito positivo, è consigliabile eseguire questa operazione mentre il server è in modalità utente singolo o quando tutta l'attività tempdb è arrestata.

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 funzionalità aumenta le prestazioni delle istruzioni 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 dispone di transazioni di cui eseguire il roll forward o il rollback.

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 opera solo sui file logici tempdb predefiniti tempdev e templog. Se vengono aggiunti ulteriori file a tempdb, è possibile compattarli dopo il riavvio di SQL Server come servizio. Tutti i file tempdb vengono ricreati durante l'avvio. Tuttavia, sono vuoti e possono essere rimossi. Per rimuovere altri file in tempdb, usare il ALTER DATABASE comando con l'opzione REMOVE FILE.

Questo metodo richiede il riavvio di SQL Server.

  1. Arrestare SQL Server.

  2. 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.

  3. 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>);

  4. Arrestare SQL Server. Per fare ciò, premere Ctrl+C nella finestra del prompt dei comandi, riavviare SQL Server come servizio e quindi verificare le dimensioni dei file tempdb.mdf e templog.ldf.

Usare il comando DBCC SHRINKDATABASE

DBCC SHRINKDATABASE riceve il parametro target_percent. Questa è la percentuale di spazio che si desidera rendere disponibile nel file di database dopo la compattazione del database. In caso di utilizzo di DBCC SHRINKDATABASE, potrebbe essere necessario riavviare SQL Server.

  1. Determinare lo spazio attualmente usato in tempdb tramite la stored procedure sp_spaceused. Calcolare quindi la percentuale di spazio disponibile lasciato per l'uso 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 utilizzato 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. Dividere ora 200 MB per 800 MB = 25%. Questo è 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.

  2. Connettersi a SQL Server con SSMS, Azure Data Studio o sqlcmd, quindi eseguire il comando Transact-SQL seguente. Sostituire <target_percent> con la percentuale desiderata:

    DBCC SHRINKDATABASE (tempdb, '<target_percent>');

Esistono limitazioni con il comando DBCC SHRINKDATABASE in tempdb. Le dimensioni di destinazione per i file di dati e di resoconto non possono essere inferiori alle dimensioni specificate al momento della creazione del database o inferiori alle ultime dimensioni impostate in modo esplicito tramite un'operazione di modifica delle dimensioni del file, ad esempio ALTER DATABASE che usa l'opzione MODIFY FILE. Un'altra limitazione di DBCC SHRINKDATABASE è il calcolo del parametro target_percentage e la relativa dipendenza dallo spazio corrente usato.

Usare il comando DBCC SHRINKFILE

Usare il comando DBCC SHRINKFILE per compattare i singoli file tempdb. 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 riceve il parametro target_size. Si tratta delle dimensioni finali desiderate per il file di database.

  1. 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 della destinazione desiderata.

  2. Connettersi a SQL Server con SSMS, Azure Data Studio o sqlcmd, quindi eseguire il comando Transact-SQL seguente per compattare i file di database specifici. 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 a una dimensione inferiore a quella originale. È possibile eseguire DBCC SHRINKFILE in uno qualsiasi dei file di dati o di resoconto. Non è possibile rendere il database inferiore alle dimensioni del database model.

Errore 8909 quando si eseguono operazioni di compattazione

In caso di utilizzo di tempdb e in caso si tenti di compattarlo usando i comandi DBCC SHRINKDATABASE o DBCC SHRINKFILE, è possibile ricevere messaggi simili ai seguenti, a seconda della 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. Per questo motivo, se l'errore si verifica al di fuori delle operazioni di compattazione, è consigliabile eseguire altre indagini.

Anche se viene restituito un messaggio 8909 all'applicazione o all'utente che esegue l'operazione di compattazione, le operazioni di compattazione andranno a buon fine.

Contenuto correlato