FILE DI RIDUCIMENTO DBC (Transact-SQL)

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzureDatabase SQL in Microsoft Fabric

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.

Usa DBCC SHRINKFILE solo quando necessario perché la riduzione è un'operazione di lunga durata e che richiede molte risorse.

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

Argomenti

file_name

Il nome logico del file è restringere.

file_id

Il numero di identificazione (ID) del file da ridurre. 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 megabyte del file. Se imposti target_size o 0 non lo specifichi, DBCC SHRINKFILE riduci il file alla sua dimensione di creazione.

È 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 un 8target_size sposta tutte le pagine usate negli ultimi 2 MB del file in qualsiasi pagina non allocata nei primi 8 MB del file. DBCC SHRINKFILE non compatta un file oltre le dimensioni dei dati archiviate necessarie. Ad esempio, se vengono utilizzati 7 MB di un file di dati da 10 MB, un'istruzione DBCC SHRINKFILE con un target_size di 6 riduce il file a soli 7 MB, non a 6 MB.

Se specifichi target_size con TRUNCATEONLY, DBCC SHRINKFILE potrebbe non liberare spazio libero alla fine del file.

VUOTOFILE

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 nessun nuovo dato venga aggiunto al file, nonostante questo file non sia di sola lettura. Puoi usare la ALTER DATABASE dichiarazione per rimuovere un file. Se usi l'istruzione ALTER DATABASE per cambiare la dimensione del file, il flag di sola lettura viene reimpostato e i dati possono essere aggiunti.

Per i contenitori di 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 di filegroup non necessari copiati in un altro contenitore. Per altre informazioni, vedere sp_filestream_force_garbage_collection. Per informazioni sulla rimozione di un container FILESTREAM, consulta la sezione corrispondente in ALTER DATABASE File e Filegroup Options

EMPTYFILEnon è supportata in database SQL di Azure, database SQL di Azure Hyperscale o SQL database in Microsoft Fabric.

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 disponibile alla fine del file non viene restituito al sistema operativo e le dimensioni fisiche del file non cambiano. Pertanto, se NOTRUNCATE viene specificato, il file non viene compattato.

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

Se target_size viene specificato con TRUNCATEONLY, lo spazio disponibile alla fine del file potrebbe non essere rilasciato.

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

CON 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, 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 ulteriori informazioni, vedi Comprendere i problemi di concorrenza con DBCC SHRINKFILE.

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

• Non puoi specificare l'opzione ABORT_AFTER_WAITNONE.
• 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 ] }

Si applica a: SQL Server (SQL Server 2022 (16.x) e versioni successive), database SQL di Azure, database SQL in Microsoft Fabric.

• SELF

  SELF è l'opzione predefinita. Esci dall'operazione di riduzione file 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

La tabella seguente descrive le colonne dei set di risultati.

Osservazioni:

DBCC SHRINKFILE si applica ai file del database corrente. Per maggiori informazioni su come modificare il database attuale, vedi USE.

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

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.

Problemi noti

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

• Nelle versioni di SQL Server precedenti a SQL Server 2025 (17.x), le pagine utilizzate dai tipi di colonne di grande oggetto (LOB) (varbinary(max),varchar(max) e nvarchar(max)) nei segmenti compressi di colonna store non possono essere spostate da DBCC SHRINKDATABASE e DBCC SHRINKFILE. Per altre informazioni, vedere Novità negli indici columnstore.

Informazioni sui problemi di concorrenza con DBCC SHRINKFILE

I comandi di riduzione del database e del file possono causare problemi di concorrenza, specialmente con la manutenzione attiva come la ricostruzione degli indici, o in ambienti di elaborazione delle transazioni online (OLTP) molto impegnativi.

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. Ciò significa anche che qualsiasi nuova query che richiede un Sch-S lock su una pagina IAM può essere messa in coda dietro l'operazione di riducimento, 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.

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

• 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

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 la dimensione del file non cambia dopo un'operazione di riduzione senza errori, prova i seguenti passaggi per verificare che il file abbia spazio libero sufficiente:

• Eseguire la query seguente.

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

• Se vuoi ridurre il file del registro delle transazioni, usa la vista dinamica di gestione sys.dm_db_log_space_usage (DMV) per vedere lo spazio utilizzato nel registro delle transazioni.

L'operazione di riduzione non può ridurre ulteriormente la dimensione del file se lo spazio libero è insufficiente.

Una ragione comune per cui un file di log delle transazioni non si riduce è l'assenza di backup regolari dei log delle transazioni. Per troncare il log, eseguire di nuovo il backup del log delle transazioni e quindi eseguire di nuovo l'operazione di DBCC SHRINKFILE. Se non è necessario il recupero in un momento determinato, considera i modelli di recupero (SQL Server) per evitare la crescita dei file di log.

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. Per esempio:

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 vista a gestione dinamica sys.dm_tran_active_snapshot_database_transactions 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, esegui uno dei seguenti passaggi:

• 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

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. 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 AdventureWorks2025 viene compattato fino a 1 MB. Per permettere al DBCC SHRINKFILE comando di ridurre il file, il file viene prima troncato impostando il modello di recupero del database su SIMPLE.

USE AdventureWorks2025;
GO

-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2025
    SET RECOVERY SIMPLE;
GO

-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2025_Log, 1);
GO

-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2025
    SET RECOVERY FULL;
GO

C. Troncare un file di dati

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

USE AdventureWorks2025;
GO

SELECT file_id,
       name
FROM sys.database_files;
GO

DBCC SHRINKFILE (1, TRUNCATEONLY);

D. Vuoto 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 AdventureWorks2025;
GO

-- Create a data file and assume it contains data.
ALTER DATABASE AdventureWorks2025
    ADD FILE (NAME = Test1data, FILENAME = 'C:\t1data.ndf', SIZE = 5 MB);
GO

-- Empty the data file.
DBCC SHRINKFILE (Test1data, EMPTYFILE);
GO

-- Remove the data file from the database.
ALTER DATABASE AdventureWorks2025
     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 viene interrotta.

USE AdventureWorks2025;
GO

SELECT file_id,
       name
FROM sys.database_files;
GO

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

Contenuto correlato

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