DBCC SHRINKFILE (Transact-SQL)

Data aggiornamento: 14 aprile 2006

Compatta le dimensioni del file di dati o di log specificato per il database corrente o svuota un file spostando i dati dal file specificato ad altri file dello stesso filegroup, consentendo la rimozione del file dal database. È possibile compattare un file fino a dimensioni inferiori rispetto a quelle specificate al momento della creazione. Le dimensioni minime del file verranno reimpostate sul nuovo valore.

Convenzioni della sintassi Transact-SQL

Sintassi

DBCC SHRINKFILE 
(
    { file_name | file_id } 
    { [ , EMPTYFILE ] 
    | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
    }
)
[ WITH NO_INFOMSGS ]

Argomenti

• file_name
  Nome logico del file che si desidera compattare.

• file_id
  Numero di identificazione (ID) del file che si desidera compattare. Per ottenere un ID di file, utilizzare la funzione di sistema FILE_IDEX o eseguire una query sulla vista del catalogo sys.database_files nel database corrente.

• target_size
  Dimensioni del file, in megabyte, espresse come valore di tipo integer. Se questo argomento viene omesso, il file viene compattato in base alle dimensioni di file predefinite. Le dimensioni predefinite corrispondono a quelle specificate al momento della creazione del file o alle ultime dimensioni impostate tramite ALTER DATABASE.

  Se viene specificato target_size, l'istruzione DBCC SHRINKFILE tenta di compattare il file fino alle dimensioni specificate. Le pagine utilizzate nella sezione di file che si desidera rendere disponibile vengono rilocate nello spazio disponibile nella sezione di file mantenuta. Nel caso di un file di dati di 10 MB, ad esempio, un'operazione di DBCC SHRINKFILE con l'argomento target_size impostato su 8 comporta la riallocazione di tutte le pagine utilizzate negli ultimi 2 MB del file in qualsiasi pagina non allocata nei primi 8 MB del file. L'istruzione DBCC SHRINKFILE non compatta un file oltre le dimensioni necessarie per l'archiviazione dei dati nel file. Se, ad esempio, vengono utilizzati 7 MB di un file di dati di 10 MB, un'istruzione DBCC SHRINKFILE con l'argomento target_size impostato su 6 compatta il file fino a 7 MB, non 6 MB.

• EMPTYFILE
  Esegue la migrazione di tutti i dati dal file specificato in altri file dello stesso filegroup. Poiché il Motore di database non consente più di posizionare i dati nel file vuoto, è possibile rimuovere tale file tramite l'istruzione ALTER DATABASE.

• 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 rimangono invariate. Pertanto, quando si specifica NOTRUNCATE, sembra che il file non venga compattato.

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

• TRUNCATEONLY
  Rilascia tutto lo spazio disponibile alla fine del file al sistema operativo senza eseguire alcuno spostamento di pagine all'interno del file. Il file di dati viene compattato solo fino all'ultimo extent allocato.

  Se è specificata l'opzione TRUNCATEONLY, target_size viene ignorato.

  TRUNCATEONLY è applicabile solo ai file di dati.

• WITH NO_INFOMSGS
  Disattiva tutti i messaggi informativi.

Set di risultati

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

Osservazioni

L'istruzione DBCC SHRINKFILE viene applicata ai file del database corrente. Per ulteriori informazioni sulla modifica del database corrente, vedere USE (Transact-SQL).

Le operazioni di DBCC SHRINKFILE possono essere interrotte in qualsiasi fase del processo. Il lavoro completato fino a quel momento viene mantenuto.

[!NOTA] In SQL Server 2005, quando un'operazione di DBCC SHRINKFILE ha esito negativo, viene generato un errore. Nelle versioni precedenti di SQL Server l'esito negativo di questa operazione non comporta la visualizzazione di un messaggio di errore.

Non è necessario che il database da compattare sia in modalità utente singolo. Durante la fase di compattazione del file, il database può essere utilizzato da altri utenti. Per la compattazione dei database di sistema, non è necessario eseguire l'istanza di SQL Server 2005 in modalità utente singolo.

Compattazione di un file di log

Per i file di log il Motore di database di SQL Server 2005 utilizza target_size per calcolare le dimensioni di destinazione dell'intero log. Pertanto, target_size corrisponde alla quantità di spazio disponibile nel log dopo la 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 fino alle dimensioni di destinazione specificate. 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 viene visualizzato 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 di queste azioni, è possibile utilizzare DBCC SHRINKFILE per liberare lo spazio rimanente. Per ulteriori informazioni, vedere Compattazione del log delle transazioni.

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 utilizzato. 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. Per ulteriori informazioni sui file di log virtuali, vedere Architettura fisica del log delle transazioni.

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 database ma le sue dimensioni aumentano di nuovo significa che lo spazio compattato è necessario per le normali operazioni. In questi casi è inutile compattare ripetutamente il database.
• L'operazione di compattazione generalmente aumenta la frammentazione degli indici del database. Questo è un ulteriore motivo per evitare di compattare ripetutamente un database.

Risoluzione dei problemi

In questa sezione viene descritto come diagnosticare e correggere eventuali problemi che possono verificarsi durante l'esecuzione del comando DBCC SHRINKFILE.

Il file non viene compattato

Se l'operazione di compattazione viene eseguita senza errori, ma sembra che le dimensioni del file siano rimaste invariate, verificare che lo spazio disponibile da rimuovere sia sufficiente eseguendo una delle operazioni seguenti:

• 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 utilizzato 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 del log a causare problemi di compattazione. Questo è in genere il risultato del mancato troncamento del file di log. È possibile troncare il log impostando il modello di recupero del database con registrazione minima o eseguendo il backup del log e quindi eseguendo nuovamente l'operazione DBCC SHRINKFILE. Per ulteriori informazioni, vedere Troncamento del log delle transazioni e Compattazione del log delle transazioni.

L'operazione di compattazione è bloccata

È possibile che le operazioni di compattazione vengano bloccate da una transazione che utilizza un livello di isolamento basato sul controllo delle versioni delle righe. Se, ad esempio, viene eseguita un'operazione DBCC SHRINK DATABASE mentre è in corso un'operazione di eliminazione di grandi dimensioni che utilizza un livello di isolamento basato sul controllo delle versioni delle righe, l'operazione di compattazione dei file viene rimandata fino al completamento dell'operazione di eliminazione. In questo caso viene registrato un messaggio informativo nel log degli errori di SQL Server (il messaggio 5202 per SHRINKDATABASE e il messaggio 5203 per SHRINKFILE) ogni cinque minuti nella prima ora e quindi ogni ora. Ad esempio, il log degli errori può contenere il messaggio di 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 significa che l'operazione di compattazione è bloccata da transazioni snapshot con timestamp precedenti a 109, ovvero all'ultima transazione completata dall'operazione di compattazione. Questo indica inoltre che la colonna transaction_sequence_num o first_snapshot_sequence_num della vista a gestione dinamica sys.dm_tran_active_snapshot_database_transactions contiene il valore 15. Se la colonna transaction_sequence_num o first_snapshot_sequence_num della vista contiene un numero inferiore rispetto all'ultima transazione completata da un'operazione di compattazione, equivalente a 109, l'operazione di compattazione viene rimandata fino al completamento di tali 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 viene terminata, le operazioni completate fino a quel momento vengono mantenute.
• Non eseguire alcuna operazione per consentire che l'operazione di compattazione venga rimandata fino al completamento della transazione di blocco.

Per ulteriori informazioni sul log degli errori di SQL Server, vedere Visualizzazione del log degli errori di SQL Server.

Autorizzazioni

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

Esempi

A. Compattazione di 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. Compattazione di un file di log fino alle dimensioni di destinazione specificate

Nell'esempio seguente il file di log nel database AdventureWorks viene compattato fino a 1 MB. Per consentire al comando DBCC SHRINKFILE di eseguire la compattazione, il file viene innanzitutto troncato impostando il modello di recupero del database con registrazione minima.

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

C. Troncamento di un file di dati

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

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

D. Svuotamento di un file

Nell'esempio seguente viene illustrata la procedura di svuotamento di un file in modo che sia possibile rimuoverlo dal database. Ai fini di questo esempio, viene innanzitutto creato un file di dati e si presuppone che tale file contenga dati.

USE AdventureWorks;
GO
-- Create a data file and assume it contains data.
ALTER DATABASE AdventureWorks 
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 AdventureWorks
REMOVE FILE Test1data;
GO

Vedere anche

Riferimento

ALTER DATABASE (Transact-SQL)
DBCC (Transact-SQL)
DBCC SHRINKDATABASE (Transact-SQL)
FILE_ID (Transact-SQL)
sys.database_files (Transact-SQL)

Altre risorse

File e filegroup fisici del database
Procedura: Compattazione di un file (SQL Server Management Studio)
Pagine ed extent

Guida in linea e informazioni

Assistenza su SQL Server 2005

Cronologia modifiche