DBCC SHRINKDATABASE (Transact-SQL)

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

Convenzioni della sintassi Transact-SQL

Sintassi

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

Argomenti

• database_name | database_id | 0
  Nome o ID del database che si desidera compattare. Se si specifica 0, viene utilizzato il database corrente.

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

• NOTRUNCATE
  Compatta i dati nei file di dati spostando le pagine allocate dalla fine di un file a pagine non allocate all'inizio del file. target_percent è facoltativo.

  Lo spazio disponibile alla fine del file non viene restituito al sistema operativo e le dimensioni fisiche del file rimangono invariate. Pertanto, quando si specifica NOTRUNCATE, sembra che il database non venga compattato.

  NOTRUNCATE è applicabile solo ai file di dati. Il file di log non è interessato.

• TRUNCATEONLY
  Rilascia tutto lo spazio disponibile alla fine del file al sistema operativo senza eseguire alcuno spostamento di pagine all'interno del file. Il file di dati viene compattato solo fino all'ultimo extent allocato. Se è specificata l'opzione TRUNCATEONLY, target_percent viene ignorato.

  TRUNCATEONLY interessa il file di log. Per troncare solo il file 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.

[!NOTA]

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

Osservazioni

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. Le dimensioni minime corrispondono a quelle specificate al momento della creazione del database o alle ultime dimensioni impostate in modo esplicito tramite un'operazione di modifica delle dimensioni dei file, ad esempio DBCC SHRINKFILE o ALTER DATABASE. Ad esempio, se è stato creato un database con dimensioni pari a 10 MB e le dimensioni sono aumentate fino a 100 MB, è possibile compattare il database fino a un minimo di 10 MB, anche se tutti i dati nel database sono stati eliminati.

Eseguire DBCC SHRINKDATABASE senza specificare l'opzione NOTRUNCATE o TRUNCATEONLY equivale a eseguire un'operazione DBCC SHRINKDATABASE con NOTRUNCATE seguita da un'operazione DBCC SHRINKDATABASE con TRUNCATEONLY.

Il database in fase di compattazione non deve essere necessariamente in modalità utente singolo. Altri utenti possono infatti utilizzare il database durante il processo di compattazione. 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.

Se viene rilevato un indice columnstore ottimizzato in memoria xVelocity, DBCC SHRINKDATABASE avrà esito negativo. L'operazione avrà esito positivo se completata prima del rilevamento dell'indice columnstore ottenendo dimensioni del database inferiori. Per completare DBCC SHRINKDATABASE, disabilitare tutti gli indici columnstore prima di eseguire DBCC SHRINKDATABASE, quindi ricompilare gli indici columnstore.

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 che sia disponibile il database mydb con un file di dati e due file di log, ognuno dei quali ha dimensioni pari a 10 MB, e che il file di dati contenga 6 MB di dati.

Per ogni file, Motore di database calcola le dimensioni di destinazione in base alle quali il file deve essere compattato. Se per DBCC SHRINKDATABASE viene specificato target_percent, Motore di database calcola le dimensioni di destinazione in modo che dopo la compattazione corrispondano alla percentuale di spazio disponibile specificata in target_percent. Ad esempio, se per target_percent si specifica il valore 25 per la compattazione di mydb, Motore di database calcola le dimensioni di destinazione del file di dati in 8 MB, ovvero 6 MB di dati e 2 MB di spazio disponibile. Pertanto, i dati vengono spostati dagli ultimi 2 MB del file di dati nello spazio disponibile della prima parte da 8 MB del file di dati e il file viene quindi compattato.

Si supponga che il file di dati di mydb contenga 7 MB di dati. Specificando 30 per target_percent, questo file di dati verrà ridotto alla percentuale disponibile di 30. Tuttavia, specificando 40 per target_percent, il file di dati non viene ridotto perché Motore di database non ridurrà un file a una dimensione inferiore a quella occupata attualmente dai dati. È inoltre possibile descrivere questo concetto in modo diverso, ovvero che il 40% di spazio disponibile + il 70% del file di dati completo (7 dei 10 MB) è maggiore del 100%. Dato che la somma della percentuale di spazio disponibile desiderata e della percentuale di spazio occupata dal file di dati è maggiore del 100% (del 10%), con valori di target_size maggiori di 30 la compattazione dei file di dati non viene eseguita.

Per i file di log Motore di database utilizza target_percent per calcolare le dimensioni di destinazione dell'intero log. Pertanto, target_percent corrisponde alla quantità di spazio disponibile nel log dopo la 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 includono alcuna parte del log logico oltre le dimensioni di destinazione del file di log, il file viene troncato correttamente e DBCC SHRINKDATABASE viene completata senza visualizzare alcun messaggio. Se invece i log virtuali includono parti del log logico oltre le dimensioni di destinazione, 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 utilizzare DBCC SHRINKDATABASE per liberare lo spazio rimanente.

Poiché è possibile compattare un file di log solo fino al limite del file di log virtuale, potrebbe essere impossibile compattare un file di log fino a ottenere dimensioni inferiori rispetto a quelle del file di log virtuale, anche se non viene utilizzato. 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 una quantità elevata di 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. Se si compatta ripetutamente un database ma le sue dimensioni aumentano di nuovo significa 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 ulteriore 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 che utilizza un livello di isolamento basato sul controllo della versione delle righe. Ad esempio, se viene eseguita un'operazione DBCC SHRINK DATABASE mentre è in corso un'operazione di eliminazione di grandi dimensioni che utilizza un livello di isolamento basato sul controllo delle versioni delle righe, l'operazione di compattazione dei file viene rimandata fino al completamento dell'operazione di eliminazione. In questo caso viene registrato un messaggio informativo nel log degli errori di SQL Server (il messaggio 5202 per SHRINKDATABASE e il messaggio 5203 per SHRINKFILE) ogni cinque minuti nella prima ora e quindi ogni 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 significa che l'operazione di compattazione è bloccata da transazioni snapshot con timestamp precedenti a 109, ovvero all'ultima transazione completata dall'operazione di compattazione. Il messaggio indica inoltre 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. Se la colonna transaction_sequence_num o first_snapshot_sequence_num della vista contiene un numero inferiore rispetto all'ultima transazione completata da un'operazione di compattazione, equivalente a 109, l'operazione di compattazione viene rimandata fino al completamento di tali 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 bloccante.

Autorizzazioni

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

Esempi

A.Compattazione di un database e specifica 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 allocato.

DBCC SHRINKDATABASE (AdventureWorks2012, TRUNCATEONLY);

Vedere anche

Riferimento

ALTER DATABASE (Transact-SQL)

DBCC (Transact-SQL)

DBCC SHRINKFILE (Transact-SQL)

Concetti

Compattare un database