DBCC SHRINKDATABASE (Transact-SQL)

Si applica a:yes SQL Server (tutte le versioni supportate) YesDatabase YesSQL di Azure Istanza yesgestita di SQL di Azure Azure Synapse Analytics

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

Topic link iconConvenzioni della sintassi Transact-SQL

Sintassi

DBCC SHRINKDATABASE   
( database_name | database_id | 0   
     [ , target_percent ]   
     [ , { NOTRUNCATE | TRUNCATEONLY } ]   
)  
[ WITH 
    NO_INFOMSGS ,
    {     
         [ WAIT_AT_LOW_PRIORITY 
            [ ( 
                  <wait_at_low_priority_option_list>
             )] 
         ] 
    }
]
       
< wait_at_low_priority_option > ::= 
 ABORT_AFTER_WAIT = { SELF | BLOCKERS } 
-- 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. Ignora target_percent se specificato con TRUNCATEONLY. Azure Synapse Analytics non supporta questa opzione.

DBCC SHRINKDATBASE con l'opzione TRUNCATEONLY influisce solo sul file di log delle transazioni del database. Per troncare il file di dati, usare DBCC SHRINKFILE invece . Per altre informazioni, vedere DBCC SHRINKFILE.

WITH NO_INFOMSGS

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

WAIT_AT_LOW_PRIORITY con operazioni di compattazione

Si applica a SQL Server 2022 (16.x) Preview

La funzionalità wait at low priority riduce la contesa di blocco. Per altre informazioni, vedere Informazioni sui problemi di concorrenza con DBCC SHRINKDATABASE.

Questa funzionalità è simile alla WAIT_AT_LOW_PRIORITY con le operazioni sugli indici online, con alcune differenze.

  • Non è possibile modificare MAX_DURATION. La durata di attesa è 60000 millisecondi (1 minuto).
  • Non è possibile specificare ABORT_AFTER_WAIT'opzione NONE.

WAIT_AT_LOW_PRIORITY

Quando un comando shrink viene eseguito in modalità WAIT_AT_LOW_PRIORITY, le nuove query che richiedono la stabilità dello schema (Sch-S) non vengono bloccate dall'operazione di compattazione in attesa fino a quando l'operazione di compattazione smette di attendere e avvia l'esecuzione. L'operazione di compattazione verrà eseguita quando è in grado di ottenere un blocco di modifica dello schema (Sch-M). Se una nuova operazione di compattazione in modalità WAIT_AT_LOW_PRIORITY non è in grado di ottenere un blocco a causa di una query a esecuzione prolungata, l'operazione di compattazione verrà infine timeout dopo 60000 millisecondi (1 minuto) e verrà chiusa senza errori.

Se una nuova operazione di compattazione in modalità WAIT_AT_LOW_PRIORITY non è in grado di ottenere un blocco a causa di una query a esecuzione prolungata, l'operazione di compattazione verrà infine timeout dopo 60000 millisecondi (1 minuto) e verrà chiusa senza errori. Ciò si verifica se l'operazione di compattazione non riesce a ottenere il blocco Sch-M a causa di query simultanee o query che contengono blocchi Sch-S. Quando si verifica un timeout, viene inviato un messaggio di errore 49516 al log degli errori di SQL Server, ad esempio: Msg 49516, Level 16, State 1, Line 134 Shrink timeout waiting to acquire schema modify lock in WLP mode to process IAM pageID 1:2865 on database ID 5. A questo punto, è sufficiente ripetere l'operazione di compattazione in modalità WAIT_AT_LOW_PRIORITY sapendo che non vi sarebbe alcun impatto sull'applicazione.

ABORT_AFTER_WAIT = [ SELF | BLOCKERS ]

SELF

SELF è l'opzione predefinita. Chiudere l'operazione di compattazione del database attualmente in esecuzione senza eseguire alcuna azione.

BLOCKERS

Terminare tutte le transazioni utente che bloccano l'operazione di compattazione del database in modo che l'operazione possa continuare. Per l'opzione BLOCKERS è necessario l'account di accesso per avere l'autorizzazione ALTER ANY CONNECTION.

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 il motore di database ha tentato di compattare.
FileId Numero di identificazione file del file che il motore di database ha tentato 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 a cui il motore di database stima che il file potrebbe essere ridotto.

Nota

Il motore di database non visualizza righe per tali file non compattate.

Commenti

Nota

In Azure Synapse, l'esecuzione di un comando di compattazione non è consigliata perché si tratta di un'operazione con utilizzo intensivo di I/O e può portare offline il pool SQL dedicato (in precedenza SQL Data Warehouse). 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 consideri che un database venga originariamente 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. Il motore di database calcola le dimensioni di destinazione per ogni file. in base alle quali il file deve essere compattato. Quando si specifica DBCC SHRINKDATABASE con target_percent, il motore di database calcola le dimensioni di destinazione in modo che siano la target_percent quantità di spazio disponibile nel file dopo la compattazione.

Ad esempio, se si specifica un target_percent di 25 per la compattazione mydb, il motore di database calcola le dimensioni di destinazione per il file di dati in modo che siano 8 MB (6 MB di dati più 2 MB di spazio disponibile). Di conseguenza, il motore di database sposta tutti i dati degli ultimi 2 MB del file di dati in qualsiasi spazio disponibile nel file di dati primi 8 MB e quindi compatta il file.

Si supponga che il file di dati di mydb contenga 7 MB di dati. Se si specifica un target_percent pari a 30, questo file di dati può essere ridotto alla percentuale gratuita di 30. Tuttavia, se si specifica un target_percent pari a 40, il file di dati non viene ridotto perché il motore di database non compatta un file con dimensioni inferiori a quelle attualmente occupate 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%. Qualsiasi target_size maggiore di 30 non ridurrà il file di dati. 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 per l'intero log. Ecco perché 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. Tuttavia, se parte del log logico rimane nei log virtuali oltre le dimensioni di destinazione, il motore di database libera il maggior numero possibile di spazio e quindi invia 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 dinamicamente dal motore di database quando i file di log vengono creati o estesi.

Informazioni sui problemi di concorrenza con DBCC SHRINKDATABASE

I comandi di compattazione dei file di database e compattazione possono causare problemi di concorrenza, in particolare con la manutenzione attiva, ad esempio la ricompilazione di indici o in ambienti OLTP occupati. Quando l'applicazione esegue query su tabelle di database, queste query acquisiranno e manterranno un blocco di stabilità dello schema (Sch-S) fino a quando le query non completano le operazioni. Quando si tenta di recuperare spazio durante l'utilizzo regolare, le operazioni di compattazione dei file di database e compattazione richiedono attualmente un blocco di modifica dello schema (Sch-M) durante lo spostamento o l'eliminazione di pagine di Mapping allocazione indici (IAM), bloccando i blocchi Sch-S necessari per le query utente. Di conseguenza, le query con esecuzione prolungata bloccano un'operazione di compattazione fino al completamento delle query. Ciò significa che tutte le nuove query che richiedono blocchi Sch-S vengono accodate anche dietro l'operazione di compattazione in attesa e verranno bloccate, con un'ulteriore esacerbazione di questo problema di concorrenza. Ciò può influire significativamente sulle prestazioni delle query dell'applicazione e causerà anche difficoltà nel completamento della manutenzione necessaria per ridurre i file di database. Introdotta nell'anteprima di SQL Server 2022 (16.x), la funzionalità di riduzione dell'attesa con priorità bassa (WLP) risolve questo problema adottando un blocco di modifica dello schema in modalità WAIT_AT_LOW_PRIORITY. Per altre informazioni, vedere WAIT_AT_LOW_PRIORITY con operazioni di compattazione.

Per altre informazioni sui blocchi Sch-S e Sch-M, vedere Guida al blocco delle transazioni e al controllo delle versioni delle righe.

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. Se si compatta ripetutamente un file di database e si nota che le dimensioni del database aumentano di nuovo, ciò indica che lo spazio disponibile è necessario per le normali operazioni. In questi casi, la compattazione ripetuta del file di database è un'operazione sprecata. Eventi di aumento automatico necessari per aumentare le prestazioni del file di 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 stampato nel log degli errori di SQL Server ogni cinque minuti nella prima ora e quindi ogni ora successiva. 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. Indica inoltre che le colonne transaction_sequence_num o first_snapshot_sequence_num nella vista a gestione dinamica sys.dm_tran_active_snapshot_database_transactions (Transact-SQL) contengono un valore pari a 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. Compattare un database e specificare 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. Compattare un database di Azure Synapse Analytics

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

D. Compattare un database con WAIT_AT_LOW_PRIORITY

Nell'esempio seguente viene eseguito un tentativo di ridurre le dimensioni dei file di dati e di log nel AdventureWorks2022 database per consentire il 20% dello spazio disponibile nel database. Se non è possibile ottenere un blocco entro un minuto, l'operazione di compattazione interromperà.

DBCC SHRINKDATABASE ([AdventureWorks2022], 20) WITH WAIT_AT_LOW_PRIORITY (ABORT_AFTER_WAIT = SELF);

Vedi anche

Passaggi successivi