DBCC SHRINKFILE (Transact-SQL)

Articolo
03/15/2023

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

Compatta le dimensioni dei file di dati e di log specificati nel database corrente. Questa operazione può essere usata per spostare dati da un file ad altri file nello stesso filegroup, svuotando il file e consentendo la rimozione del relativo database. È possibile compattare un file fino a dimensioni inferiori rispetto a quelle specificate al momento della creazione, reimpostando così le dimensioni minime sul nuovo valore.

Convenzioni di sintassi Transact-SQL

Sintassi

DBCC SHRINKFILE   
(  
    { file_name | file_id }   
    { [ , EMPTYFILE ]   
    | [ [ , target_size ] [ , { 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 }

Nota

Per visualizzare la sintassi Transact-SQL per SQL Server 2014 e versioni precedenti, vedere Documentazione delle versioni precedenti.

Argomenti

file_name

Nome logico del file da compattare.

file_id

Numero di identificazione (ID) del file da compattare. Per ottenere un ID file, usare la funzione di sistema FILE_IDEX o eseguire una query sulla vista del catalogo sys.database_files nel database corrente.

target_size

Intero che rappresenta la nuova dimensione del megabyte del file. Se non specificato o 0, DBCC SHRINKFILE riduce le dimensioni della creazione del file.

È possibile ridurre le dimensioni predefinite di un file vuoto usando DBCC SHRINKFILE <target_size>. Se si crea ad esempio un file con dimensioni pari a 5 MB e si riducono le dimensioni a 3 MB mentre il file è ancora vuoto, le dimensioni predefinite vengono impostate su 3 Mb. Questa condizione si applica solo a file vuoti in cui non sono mai stati contenuti dati.

Questa opzione non è supportata per i contenitori del filegroup FILESTREAM.

Se specificato, DBCC SHRINKFILE tenta di compattare il file in target_size. Le pagine usate nella sezione di file da liberare vengono spostate nello spazio disponibile nelle sezione di file mantenute. Ad esempio, con un file di dati da 10 MB, un'operazione DBCC SHRINKFILE con 8 target_size sposta tutte le pagine usate nell'ultimo 2 MB del file in tutte le pagine non associate nel primo 8 MB del file. DBCC SHRINKFILE non compatta un file oltre le dimensioni dei dati archiviate necessarie. Ad esempio, se viene usato un file di dati da 10 MB a 7 MB, un'istruzione DBCC SHRINKFILE con un target_size di 6 riduce il file a solo 7 MB, non a 6 MB.

EMPTYFILE

Esegue la migrazione di tutti i dati dal file specificato in altri file dello stesso filegroup. In altre parole, EMPTYFILE esegue la migrazione dei dati da un file specificato ad altri file nello stesso filegroup. EMPTYFILE assicura che non vengano aggiunti nuovi dati al file, nonostante questo file non sia di sola lettura. È possibile rimuovere il file usando l'istruzione ALTER DATABASE. Se si usa l'istruzione ALTER DATABASE per modificare le dimensioni del file, il flag di sola lettura viene reimpostato e i dati possono essere aggiunti.

Per i contenitori filegroup FILESTREAM, non è possibile usare ALTER DATABASE per rimuovere un file fino a quando FILESTREAM Garbage Collector non è stato eseguito ed eliminato tutti i file EMPTYFILE del contenitore filegroup non necessari copiati in un altro contenitore. Per altre informazioni, vedere sp_filestream_force_garbage_collection. Per informazioni sulla rimozione di un contenitore FILESTREAM, vedere la sezione corrispondente in Opzioni per file e filegroup ALTER DATABASE (Transact-SQL)

NOTRUNCATE

Sposta le pagine allocate dalla fine di un file di dati a pagine non allocate all'inizio del file specificando o meno target_percent. Lo spazio libero alla fine del file non viene restituito al sistema operativo e le dimensioni fisiche del file non cambiano. Pertanto, se NOTRUNCATE specificato, il file non viene ridotto.

NOTRUNCATE è applicabile solo ai file di dati. I file di log non sono interessati.

Questa opzione non è supportata per i contenitori del filegroup FILESTREAM.

TRUNCATEONLY

Rilascia tutto lo spazio libero alla fine del file al sistema operativo, ma non esegue alcun movimento di pagina all'interno del file. Il file di dati viene compattato solo fino all'ultimo extent allocato.

target_size viene ignorato se specificato con TRUNCATEONLY.

L'opzione TRUNCATEONLY non sposta le informazioni nel log, ma rimuove le VLF inattive dalla fine del file di log. Questa opzione non è supportata per i contenitori del filegroup FILESTREAM.

WITH NO_INFOMSGS

Disattiva tutti i messaggi informativi.

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

Si applica a: SQL Server (SQL Server 2022 (16.x) e versioni successive) e Azure SQL Database.

Quando un comando di compattazione viene eseguito in modalità WAIT_AT_LOW_PRIORITY, le nuove query che richiedono blocchi di stabilità dello schema (Sch-S) non vengono bloccate dall'operazione di compattazione in attesa finché l'operazione di compattazione non smette di attendere e avvia 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 modalità WAIT_AT_LOW_PRIORITY non può 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 in modo invisibile all'utente.

Se una nuova operazione di compattazione in modalità WAIT_AT_LOW_PRIORITY non può 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 in modo invisibile all'utente. 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 modalità WAIT_AT_LOW_PRIORITY, sapendo che non vi sarà alcun impatto sull'applicazione.

ABORT_AFTER_WAIT = [ SELF | BLOCKERS ]

Si applica a: SQL Server (SQL Server 2022 (16.x) e versioni successive) e Azure SQL Database.

SELF

Esce dall'operazione di compattazione file attualmente in esecuzione senza eseguire alcuna azione.
BLOCKERS

Termina tutte le transazioni utente che bloccano l'operazione di compattazione file, 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

La tabella seguente descrive le colonne dei 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. 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 calcolato dal motore di database. Corrisponde alle possibili dimensioni finali del file compattato.

Commenti

DBCC SHRINKFILE si applica ai file del database corrente. Per altre informazioni su come modificare il database corrente, vedere USE (Transact-SQL).

È possibile arrestare DBCC SHRINKFILE le operazioni in qualsiasi momento e qualsiasi lavoro completato viene mantenuto. Se si usa il parametro e si annulla l'operazione EMPTYFILE , il file non è contrassegnato per impedire l'aggiunta di dati aggiuntivi.

Quando un'operazione ha esito negativo, viene generato un DBCC SHRINKFILE errore.

Altri utenti possono lavorare nel database durante la compattazione dei file; il database non deve essere in modalità utente singolo. Per la compattazione dei database di sistema, non è necessario eseguire l'istanza di SQL Server in modalità utente singolo.

Se specificato con WAIT_AT_LOW_PRIORITY, la richiesta di blocco Sch-M dell'operazione di compattazione attenderà con priorità bassa quando si esegue il comando per 1 minuto. Se l'operazione viene bloccata per la durata, verrà eseguita l'azione ABORT_AFTER_WAIT specificata.

Informazioni sui problemi di concorrenza con DBCC SHRINKFILE

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. Introdotto in SQL Server 2022 (16.x), la funzionalità di attesa ridotta con priorità bassa risolve questo problema accettando 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.

Compattare un file di log

Per i file di log, il motore di database usa target_size per calcolare le dimensioni di destinazione dell'intero log. Di conseguenza, target_size è 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 SHRINKFILE tenta di compattare immediatamente ogni file di log fisico alle dimensioni di destinazione. Se invece i log virtuali includono parti del log logico oltre le dimensioni di destinazione, il motore di database libera la maggior quantità di spazio possibile e visualizza 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 SHRINKFILE è possibile usare 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 usato. Il motore di database sceglie in modo dinamico le dimensioni del file di log virtuale durante la creazione o l'estensione dei file di log.

Procedure consigliate

Quando si pianifica la compattazione di un file, 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 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.
L'operazione di compattazione generalmente aumenta la frammentazione degli indici del database. Questo è un altro motivo per evitare di compattare ripetutamente un database.
Compattare più file nello stesso database in sequenza anziché contemporaneamente. La contesa sulle tabelle di sistema può provocare blocchi e di conseguenza ritardi.

Risolvere problemi

Questa sezione descrive come diagnosticare e correggere i problemi che possono verificarsi durante l'esecuzione del DBCC SHRINKFILE comando.

Il file non viene compattato

Se le dimensioni del file restano invariate dopo un'operazione di compattazione eseguita senza errori, provare le procedure seguenti per verificare che lo spazio disponibile nel file sia sufficiente:

Eseguire la query seguente.

SELECT name
    , size / 128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0 AS AvailableSpaceInMB
FROM sys.database_files;

Eseguire il comando DBCC SQLPERF per restituire lo spazio usato nel log delle transazioni.

Se lo spazio disponibile non è sufficiente, l'operazione di compattazione non può ridurre ulteriormente le dimensioni del file.

In genere è il file di log a causare problemi di compattazione. Questo è in genere il risultato del mancato troncamento del file di log. Per troncare il log, è possibile impostare il modello di recupero del database su SIMPLE oppure eseguire di nuovo il backup del log e quindi eseguire di nuovo l'operazione DBCC SHRINKFILE .

L'operazione di compattazione è bloccata

È 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, se un'operazione di eliminazione di grandi dimensioni in esecuzione in un livello di isolamento basato sul controllo delle versioni delle righe è in corso quando viene eseguita un'operazione DBCC SHRINKDATABASE , l'operazione di compattazione attende il completamento dell'eliminazione prima di continuare. Quando si verifica DBCC SHRINKFILE questo blocco e DBCC SHRINKDATABASE le operazioni stampano un messaggio informativo (5202 per SHRINKDATABASE e 5203 per SHRINKFILE) nel log degli errori di SQL Server. Questo messaggio viene registrato ogni cinque minuti nella prima ora e quindi ogni ora. Ad esempio, se il log degli errori contiene il messaggio di errore seguente, si verificherà l'errore seguente:

DBCC SHRINKFILE for file ID 1 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 messaggio significa che l'operazione di compattazione è bloccata da transazioni snapshot con timestamp precedenti a 109, ovvero all'ultima transazione completata dall'operazione di compattazione. Indica anche le transaction_sequence_numcolonne , o first_snapshot_sequence_num nella sys.dm_tran_active_snapshot_database_transactions visualizzazione a gestione dinamica contiene un valore pari a 15. Se la colonna vista transaction_sequence_num o first_snapshot_sequence_num contiene un numero minore rispetto all'ultima transazione completata da un'operazione di compattazione (109), l'operazione di compattazione attende 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. Se l'operazione di compattazione viene terminata, 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 file di dati fino alle dimensioni di destinazione specificate

Nell'esempio seguente le dimensioni di un file di dati denominato DataFile1 nel database utente UserDB vengono compattate fino a 7 MB.

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

B. Compattare un file di log fino alle dimensioni di destinazione specificate

Nell'esempio seguente il file di log nel database AdventureWorks2022 viene compattato fino a 1 MB. Per consentire al DBCC SHRINKFILE comando di compattare il file, il file viene prima troncato impostando il modello di recupero del database su SIMPLE.

USE AdventureWorks2022;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2022
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2022_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2022
SET RECOVERY FULL;
GO

C. Troncare un file di dati

Nell'esempio seguente viene troncato il file di dati primario nel database AdventureWorks2022. Viene eseguita una query sulla vista del catalogo sys.database_files per ottenere il file_id del file di dati.

USE AdventureWorks2022;
GO
SELECT file_id, name
FROM sys.database_files;
GO
DBCC SHRINKFILE (1, TRUNCATEONLY);

D. Svuotare un file

L'esempio seguente illustra la procedura di svuotamento di un file in modo che sia possibile rimuoverlo dal database. Ai fini dell'esempio viene prima di tutto creato un file di dati che contiene dati.

USE AdventureWorks2022;
GO
-- Create a data file and assume it contains data.
ALTER DATABASE AdventureWorks2022
ADD FILE (
    NAME = Test1data,
    FILENAME = 'C:\t1data.ndf',
    SIZE = 5MB
    );
GO
-- Empty the data file.
DBCC SHRINKFILE (Test1data, EMPTYFILE);
GO
-- Remove the data file from the database.
ALTER DATABASE AdventureWorks2022
REMOVE FILE Test1data;
GO

E. Compattare un file di database con WAIT_AT_LOW_PRIORITY

L'esempio seguente prova a compattare le dimensioni di un file di dati nel database utente fino a 1 MB. Viene eseguita una query sulla vista del catalogo sys.database_files per ottenere il file_id del file di dati, in questo esempio file_id 5. Se non è possibile ottenere un blocco entro un minuto, l'operazione di compattazione interromperà.

USE AdventureWorks2022;
GO

SELECT file_id, name
FROM sys.database_files;
GO

DBCC SHRINKFILE (5, 1) WITH WAIT_AT_LOW_PRIORITY (ABORT_AFTER_WAIT = SELF);