DBCC SHRINKDATABASE (Transact-SQL)

Si applica a: sìSQL Server (tutte le versioni supportate) Sìdatabase SQL di Azure SìIstanza gestita di SQL di Azure sìAzure Synapse Analytics

Compatta le dimensioni dei file di dati e di log nel database specificato.

Icona di collegamento a un argomento Convenzioni della sintassi Transact-SQL

Sintassi

DBCC SHRINKDATABASE   
( database_name | database_id | 0   
     [ , target_percent ]   
     [ , { NOTRUNCATE | TRUNCATEONLY } ]   
)  
[ WITH NO_INFOMSGS ]  
-- Azure Synapse Analytics

DBCC SHRINKDATABASE   
( database_name   
     [ , target_percent ]   
)  
[ WITH NO_INFOMSGS ]

Nota

Per visualizzare la sintassi Transact-SQL per SQL Server 2014 e versioni precedenti, vedere Documentazione delle versioni precedenti.

Argomenti

database_name | database_id | 0
Nome o ID del database da compattare. Il valore 0 specifica che si sta usando il database corrente.

target_percent
Percentuale di spazio che si desidera rendere disponibile nel file del database dopo la compattazione.

NOTRUNCATE
Sposta le pagine assegnate dalla fine del file alle pagine non assegnate all'inizio del file. Questa azione compatta i dati all'interno del file. target_percent è facoltativo. Azure Synapse Analytics non supporta questa opzione.

Lo spazio disponibile alla fine del file non viene restituito al sistema operativo e le dimensioni fisiche del file rimangono invariate. Di conseguenza, quando si specifica NOTRUNCATE il database viene visualizzato come non compattato.

NOTRUNCATE è applicabile solo ai file di dati. NOTRUNCATE non ha effetti sul file di log.

TRUNCATEONLY
Restituisce al sistema operativo tutto lo spazio disponibile alla fine del file. Non sposta le pagine all'interno del file. Il file di dati viene compattato solo fino all'ultimo extent assegnato. target_percent viene ignorato se specificato con TRUNCATEONLY. Azure Synapse Analytics non supporta questa opzione.

TRUNCATEONLY interessa il file di log. Per troncare solo il file di dati, utilizzare DBCC SHRINKFILE.

WITH NO_INFOMSGS
Evita la visualizzazione di tutti i messaggi informativi con livello di gravità compreso tra 0 e 10.

Set di risultati

Nella tabella seguente vengono descritte le colonne del set di risultati.

Nome colonna Descrizione
DbId Numero di identificazione del database del file che Motore di database tenta di compattare.
FileId Numero di identificazione del file che Motore di database tenta di compattare.
CurrentSize Numero di pagine da 8 KB attualmente occupate dal file.
MinimumSize Numero minimo di pagine da 8 KB che il file può occupare. Il valore corrisponde alle dimensioni minime o alle dimensioni originali di un file.
UsedPages Numero di pagine da 8 KB utilizzate dal file.
EstimatedPages Numero di pagine da 8 KB calcolato da Motore di database. Corrisponde alle possibili dimensioni finali del file compattato.

Nota

Motore di database non visualizza alcuna riga per i file non compattati.

Osservazioni

Nota

L'esecuzione di questo comando non è consigliata poiché si tratta di un'operazione a elevato utilizzo di input/output che può portare il data warehouse offline. Inoltre, l'esecuzione di questo comando comporta costi per gli snapshot del data warehouse.

Per compattare tutti i file di dati e di log per un database specifico, eseguire il comando DBCC SHRINKDATABASE. Per compattare un file di dati o di log alla volta per un database specifico, eseguire il comando DBCC SHRINKFILE.

Per visualizzare la quantità corrente di spazio disponibile, ovvero non allocato, nel database, eseguire sp_spaceused.

È possibile arrestare le istruzioni DBCC SHRINKDATABASE in qualsiasi momento, senza perdere il lavoro completato.

Non è possibile ridurre il database a dimensioni inferiori a quelle minime configurate. Le dimensioni minime vengono specificate al momento della creazione del database. In alternativa, le dimensioni minime possono essere le ultime dimensioni impostate esplicitamente tramite un'operazione di modifica delle dimensioni del file. Operazioni come DBCC SHRINKFILE o ALTER DATABASE sono esempi di operazioni di modifica delle dimensioni del file.

Si supponga che un database venga creato con dimensioni pari a 10 MB. In seguito, tali dimensioni aumentano fino a 100 MB. Le dimensioni minime a cui è possibile compattare il database sono pari a 10 MB, anche se tutti i dati nel database sono stati eliminati.

Quando si esegue DBCC SHRINKDATABASE, specificare l'opzione NOTRUNCATE o l'opzione TRUNCATEONLY. In caso contrario, il risultato è lo stesso di quando si esegue un'operazione DBCC SHRINKDATABASE con NOTRUNCATE seguita da un'operazione DBCC SHRINKDATABASE con TRUNCATEONLY.

Non è necessario che il database compattato sia in modalità utente singolo. I database possono essere usati anche da altri utenti quando sono compattati e questo vale anche per i database di sistema.

Non è possibile compattare un database mentre ne viene eseguito il backup e non è possibile eseguire il backup di un database mentre è in corso un'operazione di compattazione.

Funzionamento di DBCC SHRINKDATABASE

DBCC SHRINKDATABASE compatta i file di dati uno alla volta mentre i file di log vengono compattati come se fossero inclusi in un pool di log contigui. I file vengono compattati sempre a partire dalla fine.

Si supponga di avere un paio di file di log, un file di dati e un database denominato mydb. I file di dati e di log hanno una dimensione di 10 MB ciascuno e il file di dati contiene 6 MB di dati. Per ogni file, il Motore di database calcola le dimensioni di destinazione in base alle quali il file deve essere compattato. Quando DBCC SHRINKDATABASE è specificato con target_percent, il Motore di database calcola le dimensioni di destinazione come target_percent dello spazio disponibile nel file dopo la compattazione.

Ad esempio, se si specifica un valore target_percent di 25 per la compattazione di mydb, il Motore di database calcola le dimensioni di destinazione del file di dati come 8 MB, ovvero 6 MB di dati e 2 MB di spazio disponibile. Di conseguenza, il Motore di database sposta i dati degli ultimi 2 MB del file di dati nello spazio disponibile nei primi 8 MB del file di dati e quindi compatta il file.

Si supponga che il file di dati di mydb contenga 7 MB di dati. Specificando un valore target_percent pari a 30, il file di dati può essere compattato alla percentuale disponibile di 30. Tuttavia, specificando un valore target_percent di 40, il file di dati non viene compattato perché il Motore di database non compatta un file a dimensioni minori di quelle occupate attualmente dai dati.

È possibile anche considerare il problema in un altro modo: il 40% di spazio disponibile desiderato + il 70% del file di dati completo (7 dei 10 MB) è maggiore del 100%. Con valori target_size superiori a 30 il file di dati non viene compattato. Non viene compattato perché la percentuale disponibile desiderata più la percentuale corrente occupata dal file di dati è superiore al 100%.

Per i file di log, il Motore di database usa target_percent per calcolare le dimensioni di destinazione dell'intero log. Per questa ragione target_percent è la quantità di spazio disponibile nel log dopo l'operazione di compattazione. Le dimensioni di destinazione per l'intero log vengono quindi convertite nelle dimensioni di destinazione per ogni file di log.

DBCC SHRINKDATABASE tenta di compattare immediatamente ogni file di log fisico fino alle dimensioni di destinazione specificate. Se i log virtuali non includano parti con dimensioni superiori alle dimensioni di destinazione del file di log, il file viene troncato e l'operazione DBCC SHRINKDATABASE termina senza messaggi. Se invece i log virtuali includono parti del log logico oltre le dimensioni di destinazione, il Motore di database libera la maggior quantità di spazio possibile e viene visualizzato un messaggio informativo in cui sono descritte le operazioni necessarie per estrarre le parti del log logico dai log virtuali alla fine del file. Dopo l'esecuzione di queste operazioni, è possibile usare DBCC SHRINKDATABASE per liberare lo spazio rimanente.

È possibile compattare un file di log solo entro il limite di un file di log virtuale. Ecco perché la compattazione di un file di log a dimensioni inferiori a quelle di un file di log virtuale può non essere possibile. Può non essere possibile anche se il file non viene usato. Le dimensioni del file di log virtuale vengono scelte in modo dinamico da Motore di database durante la creazione o l'estensione dei file di log.

Procedure consigliate

Quando si pianifica la compattazione di un database, considerare le informazioni seguenti:

  • Un'operazione di compattazione è più efficace dopo l'esecuzione di un'operazione che crea spazio inutilizzato, ad esempio il troncamento o l'eliminazione di una tabella.
  • La maggior parte dei database richiede spazio disponibile per lo svolgimento delle normali attività quotidiane. È possibile che, nonostante le ripetute operazioni di compattazione di un database, questo continui ad aumentare di dimensioni. Questo aumento indica che lo spazio compattato è necessario per le normali operazioni. In questi casi è inutile compattare ripetutamente il database.
  • L'operazione di compattazione generalmente aumenta la frammentazione degli indici del database. Questo è un altro motivo per evitare di compattare ripetutamente un database.
  • Se non è necessario soddisfare esigenze specifiche, non impostare l'opzione di database AUTO_SHRINK su ON.

Risoluzione dei problemi

È possibile che le operazioni di compattazione vengano bloccate da una transazione eseguita in un livello di isolamento basato sul controllo della versione delle righe. Ad esempio, si tenta di eseguire un'operazione DBCC SHRINK DATABASE mentre è in corso un'operazione di eliminazione di grandi dimensioni che usa un livello di isolamento basato sul controllo delle versioni delle righe. Quando si verifica questa situazione, l'operazione di compattazione attende fino al completamento dell'operazione di eliminazione prima di compattare i file. Mentre l'operazione di compattazione è in attesa, le operazioni DBCC SHRINKFILE e DBCC SHRINKDATABASE generano un messaggio informativo (5202 per SHRINKDATABASE e 5203 per SHRINKFILE). Questo messaggio viene generato nel log degli errori di SQL Server ogni cinque minuti nella prima ora e in seguito una volta all'ora. Ad esempio, il log degli errori può contenere il messaggio di errore seguente:

DBCC SHRINKDATABASE for database ID 9 is waiting for the snapshot   
transaction with timestamp 15 and other snapshot transactions linked to   
timestamp 15 or with timestamps older than 109 to finish.  

Questo errore indica che le transazioni snapshot con timestamp precedenti a 109 bloccano l'operazione di compattazione. La transazione indicata è l'ultima transazione completata dall'operazione di compattazione. Il messaggio indica anche che la colonna transaction_sequence_num o first_snapshot_sequence_num nella DMV sys.dm_tran_active_snapshot_database_transactions (Transact-SQL) contiene un valore 15. La colonna transaction_sequence_num o first_snapshot_sequence_num nella vista può contenere un numero minore rispetto all'ultima transazione completata da un'operazione di compattazione (109). In questo caso, l'operazione di compattazione attenderà il completamento delle transazioni.

Per risolvere il problema, è possibile eseguire una delle attività seguenti:

  • Terminare la transazione che blocca l'operazione di compattazione.
  • Terminare l'operazione di compattazione. Il lavoro completato fino a quel momento viene mantenuto.
  • Non eseguire alcuna operazione per consentire che l'operazione di compattazione venga rimandata fino al completamento della transazione di blocco.

Autorizzazioni

È richiesta l'appartenenza al ruolo predefinito del server sysadmin o al ruolo predefinito del database db_owner .

Esempi

R. Compattazione di un database e impostazione di una percentuale di spazio disponibile

Nell'esempio seguente vengono ridotte le dimensioni dei file di dati e di log nel database utente UserDB per ottenere il 10% di spazio disponibile nel database.

DBCC SHRINKDATABASE (UserDB, 10);  
GO  

B. Troncamento di un database

Nell'esempio seguente i file di dati e di log nel database di esempio AdventureWorks vengono compattati fino all'ultimo extent assegnato.

DBCC SHRINKDATABASE (AdventureWorks2012, TRUNCATEONLY);  

C. Compattazione di un database di Azure Synapse Analytics

DBCC SHRINKDATABASE (database_A);
DBCC SHRINKDATABASE (database_B, 10); 

Vedere anche

ALTER DATABASE (Transact-SQL)
DBCC (Transact-SQL)
DBCC SHRINKFILE (Transact-SQL)
Compattare un database