DBCC SHRINKDATABASE (Transact-SQL)

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzureAzure Synapse AnalyticsDatabase SQL in Microsoft Fabric

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

Non considerare le operazioni di riduzione come una normale manutenzione. I file di dati e di log che aumentano a causa di operazioni aziendali regolari e ricorrenti non richiedono operazioni di compattazione.

Convenzioni relative alla 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 ]

Argomenti

{ database_name | database_id | 0 }

Il nome o l'ID del database da restringere. Un valore di 0 specifica il database corrente.

target_percent

La percentuale di spazio libero da lasciare nel file del database dopo il completamento dell'operazione di ridurre.

Se specifichi target_percent con TRUNCATEONLY, l'operazione di riduzione potrebbe non liberare spazio libero alla fine del file.

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 compattato quando si specifica NOTRUNCATE.

NOTRUNCATE Si applica solo ai file 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. Azure Synapse Analytics non supporta questa opzione.

Se specifichi target_percent con TRUNCATEONLY, l'operazione di riduzione potrebbe non liberare spazio libero alla fine del file.

CON 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, database SQL di Azure, Istanza gestita di SQL di Azure, database SQL in Microsoft Fabric

La funzione di attesa a bassa priorità riduce la contenzione del blocco durante l'operazione di ridurre. 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 puoi specificare ABORT_AFTER_WAIT l'opzione NONE.
• Non puoi impostare l'opzione MAX_DURATION . Il timeout di blocco a bassa priorità per un'operazione di riduzione è sempre di un minuto.

WAIT_AT_LOW_PRIORITY

Quando un comando di riduzione viene eseguito in WAIT_AT_LOW_PRIORITY modalità, le query che richiedono blocchi di stabilità dello schema (Sch-S) sulle pagine Index Allocation Map (IAM) non vengono bloccate dall'operazione di ridurre. Tuttavia, l'operazione di riduzione può essere bloccata da un Sch-S blocco su una pagina IAM. Shrink continua a essere eseguito solo quando è in grado di ottenere un blocco di modifica dello schema (Sch-M) su una pagina IAM che richiede.

Se un'operazione di riduzione in WAIT_AT_LOW_PRIORITY modalità non riesce a ottenere questo blocco a causa di una query di lunga durata che contiene un Sch-S blocco, l'operazione di riduzione scade con l'errore 49516, 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.

{ ABORT_AFTER_WAIT = [ SELF | BLOCCHI ] }

• SELF

  SELF è l'opzione predefinita. Esci dall'operazione di riduzione del database attualmente in esecuzione senza prendere ulteriori azioni.

• BLOCKERS

  Termina tutte le transazioni utente che bloccano l'operazione di compattazione file, in modo che l'operazione possa continuare. L'opzione BLOCKERS richiede che il login abbia il permesso ALTER ANY CONNECTION di OR KILL DATABASE CONNECTION .

Set di risultati

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

Osservazioni:

Per compattare tutti i file di dati e 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 tutte le operazioni completate vengono mantenute.

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

Puoi specificare l'opzione NOTRUNCATE o l'opzione TRUNCATEONLY quando esegui DBCC SHRINKDATABASE. Se non specifichi nessuna delle due opzioni, il risultato è lo stesso che se esegui un'operazione DBCC SHRINKDATABASE con NOTRUNCATE seguito 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.

Nei pool SQL di Azure Synapse, evita di eseguire un comando shrink perché è un'operazione intensiva di I/O che può mettere offline il tuo pool SQL dedicato (precedentemente SQL DW). Questo comando influisce anche sul costo degli snapshot del tuo data warehouse.

Problemi noti

Si applica a: SQL Server, database SQL di Azure, Istanza gestita di SQL di Azure, Azure Synapse Analytics dedicato SQL pool

• In SQL Server 2022 (16.x) e versioni precedenti, le pagine utilizzate dai tipi di colonne LOB (varbinary(max), varchar(max) e nvarchar(max)) nei segmenti compressi di columnstore non possono essere spostate da DBCC SHRINKDATABASE e DBCC SHRINKFILE. Per altre informazioni, vedere Novità negli indici columnstore.

Funzionamento di DBCC SHRINKDATABASE

DBCC SHRINKDATABASE compatta i file di dati per ogni file, ma riduce i file di log come se tutti i file di log fossero presenti in un pool di log contiguo. I file vengono compattati sempre a partire dalla fine.

Supponiamo di avere due file di log e un file dati in un database chiamato 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. Questo valore è la dimensione target del file dopo la riduzione del file. Quando specifichi DBCC SHRINKDATABASE con target_percent, il motore di database calcola la dimensione target come la target_percent quantità di spazio libero nel file dopo la diminuzione.

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, se si specifica un target_percent di 40, il file di dati non viene ridotto perché non è possibile creare spazio disponibile sufficiente nella dimensione totale corrente del file di dati.

Si può pensare a questo problema in un altro modo: il 40% dello spazio disponibile desiderato + il 70% del file di dati completo (7 MB su 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 nessuna parte del log logico rimane nei log virtuali oltre la dimensione target del file log, DBCC SHRINKDATABASE il file tronca con successo e termina senza alcun messaggio. 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. Il messaggio descrive le azioni per spostare il log logico fuori dai log virtuali alla fine del file. Dopo aver eseguito le azioni, usalo DBCC SHRINKDATABASE per liberare lo spazio rimasto.

Puoi solo ridurre un file di log a un confine virtuale di file di log. Ecco perché non è possibile ridurre un file di log a una dimensione inferiore a quella di un file di log virtuale. Il motore di database sceglie dinamicamente la dimensione del file di log virtuale quando crea o estende i file di log.

Informazioni sui problemi di concorrenza con DBCC SHRINKDATABASE

I comandi di riduzione del database e del file di riduzione possono causare problemi di concorrenza, specialmente con la manutenzione attiva come la ricostruzione degli indici, o in ambienti OLTP molto frequentati.

Ad esempio, una query utente potrebbe acquisire un blocco di stabilità dello schema (Sch-S) su una pagina Index Allocation Map (IAM) e mantenerla fino al completamento. Quando si tenta di recuperare spazio durante l'uso regolare, le operazioni di riduzione del database e riduzione file richiedono un blocco di modifica dello schema (Sch-M) quando si spostano o si cancellano le pagine IAM, bloccando i Sch-S blocchi necessari per le query dell'utente. Di conseguenza, le query di lunga durata possono bloccare un'operazione di ridurre. Questo comportamento significa anche che qualsiasi nuova query che richiede un Sch-S blocco su una pagina IAM può essere inserita in coda dietro l'operazione di ridurre, aggravando ulteriormente questo problema di concorrenza.

Introdotta in SQL Server 2022 (16.x), la funzione di attesa a bassa priorità per le operazioni di riduzione risolve questo problema adottando il blocco di modifica dello schema sulle pagine IAM in questa WAIT_AT_LOW_PRIORITY modalità. Per altre informazioni, vedere WAIT_AT_LOW_PRIORITY con operazioni di compattazione.

Per maggiori informazioni sui Sch-S blocchi e Sch-M i blocchi, consulta la guida al blocco delle transazioni e al versione 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 uno spazio libero per le operazioni quotidiane regolari. Se riduci ripetutamente un file di database e noti che la dimensione del database cresce di nuovo, questa crescita indica che le operazioni regolari richiedono spazio libero. In questi casi, ridurre ripetutamente il file del database è controproducente. La crescita del file necessaria per allocare nuovo spazio dopo il ritiro può ostacolare le prestazioni.

• Un'operazione di riduzione non preserva lo stato di frammentazione degli indici nel database e può aumentare la frammentazione dell'indice, il che potrebbe ridurre la velocità di lettura di I/O per query che utilizzano scansioni grandi.

• A meno che non si disponga di un requisito specifico, non impostare l'opzione AUTO_SHRINK di database su ON.

• Se devi ridurre i file dati di un grande database, considera l'uso dello script PowerShell di ShrinkDriver . Lo script automatizza e semplifica il processo di ridurre, trasformandolo in un'unica operazione osservabile e riprendibile. Lo script riduce più file in parallelo, ritenta quando interrotto e genera report di stato dettagliati durante l'esecuzione.

Risoluzione dei problemi

È possibile che le operazioni di compattazione siano bloccate da una transazione eseguita in un livello di isolamento basato sul controllo delle versioni delle righe. Ad esempio, esegui DBCC SHRINKDATABASE mentre è in corso una grande operazione di cancellazione che viene eseguita sotto un livello di isolamento basato su versioning di righe. In questo caso, l'operazione di riduzione del contenuto attende che l'operazione di cancellazione si completi prima di ridurre i file. Quando l'operazione di compattazione attende DBCC SHRINKFILE e le operazioni stampano un messaggio informativo (5202 per DBCC SHRINKDATABASE e 5203 per SHRINKDATABASESHRINKFILE ). Questo messaggio viene stampato nel log degli errori di SQL Server ogni cinque minuti nella prima ora e poi 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 significa che le transazioni snapshot con timestamp più vecchi di 109 bloccano l'operazione di ridurre. La transazione indicata è l'ultima transazione completata dall'operazione di compattazione. Indica inoltre che le transaction_sequence_num colonne o first_snapshot_sequence_num nella vista di gestione dinamica sys.dm_tran_active_snapshot_database_transactions contengono un valore di 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 tal caso, l'operazione di compattazione attende il completamento di tali transazioni.

Per risolvere il problema, puoi fare una delle seguenti azioni:

• 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

Gli esempi di codice in questo articolo usano il database di esempio AdventureWorks2025 o AdventureWorksDW2025, che è possibile scaricare dalla home page Microsoft SQL Server Samples and Community Projects.

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 AdventureWorks2025 vengono compattati fino all'ultimo extent assegnato.

DBCC SHRINKDATABASE (AdventureWorks2025, TRUNCATEONLY);

C. Compattazione di un database di Azure Synapse Analytics

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

D. Ridurre 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 AdventureWorks2025 per ottenere il 20% di spazio disponibile nel database. Se non è possibile ottenere un blocco entro un minuto, l'operazione di riduzione viene interrotta.

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

Contenuto correlato

• Compattare un database
• Compattare un file
• FILE DI RIDUCIMENTO DBC (Transact-SQL)
• Considerazioni sulle impostazioni di aumento e compattazione automatici in SQL Server
• File di database e gruppi di file
• sys.databases (Transact-SQL)
• sys.database_files (Transact-SQL)
• ALTER DATABASE (Transact-SQL)
• Gestire lo spazio dei file per i database in database SQL di Azure