DBCC SHRINKDATABASE (Transact-SQL)

  • Articolo

Si applica a: SQL Server Azure SQL DatabaseIstanza gestita di SQL di AzureAzure Synapse Analytics

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

Convenzioni di sintassi Transact-SQL

Sintassi

Sintassi per SQL Server:

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

    {     
         [ WAIT_AT_LOW_PRIORITY 
            [ ( 
                  <wait_at_low_priority_option_list>
             )] 
         ] 
         
         [ , NO_INFOMSGS]
    }
]

< wait_at_low_priority_option_list > ::=  
    <wait_at_low_priority_option>
    | <wait_at_low_priority_option_list> , <wait_at_low_priority_option>

< wait_at_low_priority_option > ::=
  ABORT_AFTER_WAIT = { SELF | BLOCKERS }

Sintassi per 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 del database o ID da rieseguire. Il valore 0 specifica che si sta usando il database corrente.

target_percent

Percentuale di spazio libero che si desidera lasciare nel file di database dopo che il database è stato ridotto.

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, il database non viene ridotto quando si specifica NOTRUNCATE.

NOTRUNCATE è applicabile solo ai file di dati. NOTRUNCATE non influisce 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 SHRINKDATABASE con l'opzione TRUNCATEONLY influisce solo sul file di log delle transazioni del database. Per troncare il file di dati, usare invece DBCC SHRINKFILE. 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) e versioni successive, Azure SQL Database, Istanza gestita di SQL di Azure

La funzionalità di attesa con priorità bassa riduce la contesa dei blocchi. Per altre informazioni, vedere Informazioni sui problemi di concorrenza con DBCC SHRINKDATABASE.

Questa funzionalità è simile a WAIT_AT_LOW_PRIORITY con operazioni sugli indici online, ma presenta alcune differenze.

  • Non è possibile specificare l'opzione ABORT_AFTER_WAIT come NONE.

WAIT_AT_LOW_PRIORITY

Quando viene eseguito un comando di compattazione in WAIT_AT_LOW_PRIORITY modalità, 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 avviare l'esecuzione. L'operazione di compattazione verrà eseguita quando può ottenere un blocco di modifica dello schema (Sch-M). Se una nuova operazione di compattazione in WAIT_AT_LOW_PRIORITY modalità non riesce a ottenere un blocco a causa di una query a esecuzione prolungata, l'operazione di compattazione verrà infine timeout dopo 1 minuto per impostazione predefinita e verrà chiusa senza errori.

Se una nuova operazione di compattazione in WAIT_AT_LOW_PRIORITY modalità non riesce a ottenere un blocco a causa di una query a esecuzione prolungata, l'operazione di compattazione verrà infine timeout dopo 1 minuto per impostazione predefinita e verrà chiusa senza errori. Questo avviene se l'operazione di compattazione non può ottenere il blocco Sch-M a causa di query simultanee o query che contengono blocchi Sch-S. In caso di 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 WAIT_AT_LOW_PRIORITY modalità sapendo che non vi sarebbe alcun impatto sull'applicazione.

ABORT_AFTER_WAIT = [ SELF | BLOCKERS ]

  • SELF

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

  • BLOCKERS

    Termina tutte le transazioni dell'utente che bloccano l'operazione di compattazione del database, in modo che l'operazione possa continuare. L'opzione BLOCKERS richiede che l'account di accesso disponga ALTER ANY CONNECTION dell'autorizzazione.

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 tenta di compattare.
FileId Numero di identificazione del file che il 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 stimato dal motore di database. Corrisponde alle possibili dimensioni finali del file compattato.

Nota

Il motore di database non visualizza alcuna riga per i file non compattati.

Commenti

Nota

In Azure Synapse non è consigliabile eseguire un comando di compattazione perché è un'operazione con utilizzo intensivo di I/O e può impostare il pool SQL dedicato (in precedenza SQL DW) offline. Inoltre, l'esecuzione di questo comando comporta costi per gli snapshot del data warehouse.

Per compattare tutti i dati e i file di log per un database specifico, eseguire il DBCC SHRINKDATABASE comando . 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.

DBCC SHRINKDATABASE le operazioni possono essere arrestate in qualsiasi momento del processo e vengono mantenute tutte le operazioni completate.

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 dei file.

Si supponga che un database venga creato inizialmente 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.

Specificare l'opzione o l'opzione NOTRUNCATETRUNCATEONLY quando si esegue DBCC SHRINKDATABASE. Se non si esegue un'operazione, il risultato è uguale a se si esegue un'operazione DBCC SHRINKDATABASE con NOTRUNCATE seguito eseguendo 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.

Se specificato con WAIT_AT_LOW_PRIORITY, la richiesta di blocco Sch-M dell'operazione di compattazione attende con priorità bassa durante l'esecuzione del comando per 1 minuto. Se l'operazione viene bloccata per la durata, verrà eseguita l'azione ABORT_AFTER_WAIT specificata.

Funzionamento di DBCC SHRINKDATABASE

DBCC SHRINKDATABASE riduce i file di dati in base a un file, ma compatta i file di log come se tutti i file di log esistano in un pool di log contiguo. 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 finali. in base alle quali il file deve essere compattato. Quando DBCC SHRINKDATABASE viene specificato con target_percent, il motore di database calcola le dimensioni di destinazione per essere la quantità di spazio disponibile target_percent 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 la dimensione finale del file di dati pari a 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 di 30, il file di dati può essere compattato alla percentuale disponibile di 30. Tuttavia, specificando un target_percent di 40 non compatta il file di dati perché non è possibile creare spazio libero sufficiente nella dimensione totale corrente del file di dati.

È possibile pensare a questo problema in un altro modo: il 40% voleva spazio libero + il 70% del file di dati completo (7 MB di 10 MB) è superiore al 100%. Qualsiasi target_percent 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 finali 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 alle dimensioni di destinazione. Se i log virtuali non includano parti con dimensioni superiori alle dimensioni di destinazione del file di log, Il file viene quindi troncato e DBCC SHRINKDATABASE completato senza messaggi. Se invece i log virtuali includono parti del log logico oltre le dimensioni finali, il motore di database libera la maggior quantità di spazio possibile e genera 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 delle azioni, DBCC SHRINKDATABASE è possibile usare 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 dal motore di database durante la creazione o l'estensione dei file di log.

Informazioni sui problemi di concorrenza con DBCC SHRINKDATABASE

I comandi di compattazione database e compattazione file possono causare problemi di concorrenza, in particolare con la manutenzione attiva, ad esempio la ricompilazione di indici o negli ambienti OLTP molto impegnati. Quando l'applicazione esegue query su tabelle di database, queste query acquisiscono e mantengono un blocco di stabilità dello schema (Sch-S) finché non completano le operazioni. Quando si tenta di recuperare spazio durante il normale utilizzo, attualmente le operazioni di compattazione del database e dei file richiedono un blocco di modifica dello schema (Sch-M) durante lo spostamento o l'eliminazione di pagine IAM (Index Allocation Map), bloccando così i blocchi Sch-S necessari per le query dell'utente. Di conseguenza, le query a esecuzione prolungata bloccano un'operazione di compattazione finché non vengono completate. Ciò significa che anche tutte le nuove query che richiedono blocchi Sch-S verranno accodate dietro l'operazione di compattazione in attesa e bloccate, aggravando ancora il problema di concorrenza. Questo può influire significativamente sulle prestazioni delle query dell'applicazione, causando inoltre difficoltà nel completamento della manutenzione necessaria per compattare i file di database. Introdotta in SQL Server 2022 (16.x), la funzionalità di attesa compatta con priorità bassa (WLP) risolve questo problema prendendo un blocco di modifica dello schema in WAIT_AT_LOW_PRIORITY modalità. 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 sue dimensioni aumentano di nuovo, significa che lo spazio libero è necessario per le normali operazioni. In questi casi è inutile compattare ripetutamente il file di database. Gli eventi di aumento automatico necessari per aumentare il file di database impediscono le prestazioni.
  • L'operazione di compattazione generalmente aumenta la frammentazione degli indici del database. Questo è un altro motivo per evitare di compattare ripetutamente un database.
  • A meno che non si disponga di un requisito specifico, non impostare l'opzione di AUTO_SHRINK database su ON.

Risolvere 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, un'operazione di eliminazione di grandi dimensioni in esecuzione in un livello di isolamento basato sul controllo delle versioni di riga è in corso quando viene eseguita un'operazione DBCC SHRINKDATABASE . Quando si verifica questa situazione, l'operazione di compattazione attende fino al completamento dell'operazione di eliminazione prima di compattare i file. Quando l'operazione di compattazione attende DBCC SHRINKFILE e le operazioni stampano un messaggio informativo (5202 per SHRINKDATABASE e 5203 per SHRINKFILEDBCC SHRINKDATABASE ). 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. Indica anche le transaction_sequence_num colonne o first_snapshot_sequence_num nella visualizzazione di gestione dinamica di sys.dm_tran_active_snapshot_database_transactions (Transact-SQL) che contiene un valore pari a 15. La transaction_sequence_num colonna o first_snapshot_sequence_num nella vista potrebbe contenere un numero minore dell'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. Troncare un database

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

DBCC SHRINKDATABASE (AdventureWorks2022, TRUNCATEONLY);

C. Compattazione di 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 si tenta di ridurre le dimensioni dei file di dati e di log nel database AdventureWorks2022 per ottenere il 20% di spazio disponibile nel database. Se un blocco non può essere ottenuto 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