Compattare un file

Articolo
07/02/2024

Si applica a: SQL Server , Database SQL di Azure e Istanza gestita di SQL di Azure

Questo articolo illustra come compattare un file di dati o di log in SQL Server usando SQL Server Management Studio o Transact-SQL.

Compattando i file di dati si recupera spazio spostando le pagine di dati dalla fine del file allo spazio non occupato più vicino all'inizio del file. Quando alla fine del file viene creato sufficiente spazio libero, le pagine di dati possono essere deallocate e restituite al file system.

Limiti

Le dimensioni del file di dati primario non possono essere inferiori a quelle del file primario nel database model.

Consigli

Un'operazione di compattazione è più efficace dopo l'esecuzione di un'operazione che crea una quantità elevata di spazio di archiviazione inutilizzato, ad esempio un'istruzione DELETE di grandi dimensioni o 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 le dimensioni del file di database incidono negativamente sulle prestazioni.
I dati spostati per ridurre un file possono essere dispersi in qualsiasi percorso disponibile nel file, provocando la frammentazione dell'indice e rallentando le prestazioni di query che eseguono ricerche in un intervallo dell'indice Per eliminare la frammentazione, valutare la possibilità di ricompilare gli indici sul file dopo la compattazione.
Se non è necessario soddisfare esigenze specifiche, non impostare l'opzione di database AUTO_SHRINK su ON.

Osservazioni:

Le operazioni di compattazione in corso possono bloccare altre query nel database e possono a loro volta essere bloccate da query già in corso. Introdotte in SQL Server 2022 (16.x), le operazioni di compattazione dei file hanno un'opzione di WAIT_AT_LOW_PRIORITY. Questa caratteristica è una nuova opzione aggiuntiva per DBCC SHRINKDATABASE e DBCC SHRINKFILE. Se una nuova operazione di compattazione in modalità WAIT_AT_LOW_PRIORITY non può ottenere i blocchi necessari a causa di una query con esecuzione prolungata già in corso, l'operazione di compattazione si arresterà dopo un minuto e verrà chiusa in modo automatico, impedendo che altre query vengano bloccate. WAIT_AT_LOW_PRIORITY si applica ai file di dati (.mdf & ndf). Non si applica ai file registro transazioni. Per altre informazioni, vedere DBCC SHRINKFILE.

Autorizzazioni

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

Usare SQL Server Management Studio (SSMS)

Compattare un file di dati o di resoconto usando SSMS

In Esplora oggetticonnettersi a un'istanza del motore di database di SQL Server e, successivamente, espanderla.
Espandere Database , quindi fare clic con il pulsante destro del mouse sul database che si desidera compattare.
Scegliere Attività, scegliere Compatta, quindi selezionare File.

Database
Consente di visualizzare il nome del database selezionato.

Tipo di file
Consente di selezionare il tipo di file. È possibile scegliere tra file di Dati e file di Log . La selezione predefinita è Dati. La selezione di un tipo di filegroup diverso determina la conseguente modifica delle selezioni negli altri campi.

Filegroup
Consente di selezionare un filegroup nell'elenco dei filegroup associato al Tipo file selezionato in precedenza. La selezione di un filegroup diverso determina la conseguente modifica delle selezioni negli altri campi.

Nome file
Consente di selezionare un file nell'elenco dei file disponibili relativo al filegroup e al tipo di file selezionati.

Location
Visualizza il percorso completo del file attualmente selezionato. Il percorso non è modificabile, ma può essere copiato negli Appunti.

Spazio allocato
In caso di file di dati, visualizza lo spazio attualmente allocato. In caso di file di log, visualizza lo spazio attualmente allocato calcolato in base all'output di DBCC SQLPERF(LOGSPACE).

Spazio disponibile
In caso di file di dati, visualizza lo spazio attualmente disponibile calcolato in base all'output di DBCC SHOWFILESTATS(fileid). In caso di file di log, visualizza lo spazio attualmente disponibile calcolato in base all'output di DBCC SQLPERF(LOGSPACE).

Rilascia spazio inutilizzato
Causa il rilascio al sistema operativo dello spazio non usato nei file e compatta il file fino all'ultimo extent allocato, riducendo le dimensioni del file senza spostare i dati. Non viene eseguito alcun tentativo di rilocazione delle righe in pagine non allocate.

Riorganizza le pagine prima di rilasciare lo spazio inutilizzato
Equivale a eseguire DBCC SHRINKFILE specificando le dimensioni del file di destinazione. Quando questa opzione è selezionata, è necessario specificare le dimensioni del file di destinazione nella casella Dimensioni file compattato .

Dimensioni file compattato
Consente di specificare le dimensioni del file di destinazione per l'operazione di compattazione. Le dimensioni non possono essere inferiori allo spazio allocato o superiori del numero di extent totali allocati al file. L'immissione di un valore non compreso nel limite minimo o massimo determinerà il ripristino dei valori minimo e massimo in seguito alla modifica dello stato attivo o alla pressione di un pulsante sulla barra degli strumenti.

Svuota il file eseguendo la migrazione dei dati in altri file nello stesso filegroup
Consente di eseguire la migrazione di tutti i dati dal file specificato. Questa opzione consente l'eliminazione del file tramite l'istruzione ALTER DATABASE. Questa opzione equivale a eseguire DBCC SHRINKFILE con l'opzione EMPTYFILE. EMPTYFILE non è supportato in database SQL di Azure o database SQL di Azure Hyperscale.
Selezionare il tipo e il nome del file.
Facoltativamente, selezionare la casella di controllo Rilascia spazio inutilizzato .

Se selezionata, questa opzione consente di rilasciare al sistema operativo lo spazio inutilizzato del file e di compattare il file fino all'ultimo extent allocato, riducendo quindi le dimensioni del file senza spostare i dati.
Facoltativamente, selezionare la casella di controllo Riorganizza i file prima di rilasciare lo spazio inutilizzato . Se si seleziona questa opzione, è necessario specificare il valore di Dimensioni file compattato . Per impostazione predefinita, questa opzione è deselezionata.

Se selezionata, questa opzione consente di rilasciare al sistema operativo lo spazio inutilizzato del file e di spostare, se possibile, le righe in pagine non allocate.
Facoltativamente, immettere la percentuale massima di spazio libero da rendere disponibile nel database dopo la compattazione. I valori consentiti sono compresi tra 0 e 99. Questa opzione è disponibile solo se l'opzione Riorganizza i file prima di rilasciare lo spazio inutilizzato è abilitata.
Facoltativamente, selezionare la casella di controllo Svuota il file eseguendo la migrazione dei dati in altri file nello stesso filegroup .

Se selezionata, questa opzione consente di spostare tutti i dati dal file selezionato ad altri file nel filegroup. È quindi possibile eliminare il file vuoto. L'opzione è equivalente all'esecuzione dell'istruzione DBCC SHRINKFILE con l'opzione EMPTYFILE.
Seleziona OK.

Usare Transact-SQL

Compattare un file di dati o di resoconto usando Transact-SQL

Connettersi al motore di database di.
Nella barra Standard selezionare Nuova query.
Copiare e incollare l'esempio seguente nella finestra di query e selezionare Esegui. In questo esempio si usano DBCC SHRINKFILE per compattare le dimensioni di un file di dati denominato DataFile1 nel database UserDB a 7 MB.

USE UserDB;
GO
DBCC SHRINKFILE (DataFile1, 7);
GO