DBCC SHRINKDATABASE (Transact-SQL)

• 07/17/2017
• 8 minuti per leggere
• • p
  • M
  • j
  • i
  • K
  • +12

Si applica a: SQL Server (tutte le versioni supportate) Azure SQL Database Azure Synapse Analytics

Riduce la dimensione dei file di dati e di log nel database specificato.

Convenzioni sintassi Transact-SQL

Sintassi

DBCC SHRINKDATABASE ( database_name | database_id | 0 ) 

-- Azure Synapse AnalyticsDBCC SHRINKDATABASE ( database_name ) 

Arguments

database_name | database_id | 0
È il nome del database o l’ID da rimpicciolire. 0 specifica che viene usato il database corrente.

target_percent
È la percentuale di spazio libero che volete rimanga nel file del database dopo che il database è stato ristretto.

NOTRUNCATE
Muove le pagine assegnate dalla fine del file alle pagine non assegnate nella parte anteriore del file. Questa azione compatta i dati all’interno del file. target_percent è opzionale. Azure Synapse Analytics non supporta questa opzione.

Lo spazio libero alla fine del file non viene restituito al sistema operativo e la dimensione fisica del file non cambia. Come tale, il database sembra non ridursi quando si specifica NOTRUNCATE.

NOTRUNCATE è applicabile solo ai file di dati. NOTRUNCATE non ha effetto sul file di log.

TRUNCATEONLY
Lascia tutto lo spazio libero alla fine del file al sistema operativo. Non sposta nessuna pagina all’interno del file. Il file di dati si restringe solo all’ultima estensione assegnata. Ignora target_percent se specificato con TRUNCATEONLY. Azure Synapse Analytics non supporta questa opzione.

TRUNCATEONLY influenza il file di log. Per troncare solo il file di dati, usa DBCC SHRINKFILE.

WITH NO_INFOMSGS
Sospende tutti i messaggi informativi che hanno livelli di gravità da 0 a 10.

Set di risultati

La seguente tabella descrive le colonne nel set di risultati.

Nome della colonna	Descrizione
DbId	Numero di identificazione del database del file che il Database Engine ha cercato di ridurre.
FileId	Numero di identificazione del file che il Database Engine ha cercato di ridurre.
CurrentSize	Numero di pagine 8-KB che il file occupa attualmente.
MinimumSize	Numero di pagine 8-KB che il file potrebbe occupare, al minimo. Questo valore corrisponde alla dimensione minima o alla dimensione creata originariamente di un file.
UsedPages	Numero di 8-KB pagine attualmente usate dal file.
EstimatedPages	Numero di 8-KB pagine che il Database Engine stima che il file possa essere ridotto.

Remarks

Per ridurre tutti i dati e i file di log per un database specifico, esegui il comando DBCC SHRINKDATABASE. Per ridurre un file di dati o di log alla volta per un database specifico, eseguire il comando DBCC SHRINKFILE.

Per visualizzare la quantità attuale di spazio libero (non allocato) nel database, eseguire sp_spaceused.

Le operazioni DBCC SHRINKDATABASE possono essere fermate in qualsiasi punto del processo, e qualsiasi lavoro completato viene mantenuto.

Il database non può essere più piccolo della dimensione minima configurata del database. Si specifica la dimensione minima quando il database viene creato originariamente. Oppure, la dimensione minima può essere l’ultima dimensione esplicitamente impostata utilizzando un’operazione di modifica della dimensione del file. Operazioni come DBCC SHRINKFILE o ALTER DATABASE sono esempi di operazioni di modifica della dimensione del file.

Diciamo che un database è originariamente creato con una dimensione di 10 MB. Poi, cresce fino a 100 MB. Il più piccolo a cui il database può essere ridotto è 10 MB, anche se tutti i dati nel database sono stati cancellati.

Specificare l’opzione NOTRUNCATE o l’opzione TRUNCATEONLY quando si esegue DBCC SHRINKDATABASE. Se non lo fai, il risultato è lo stesso di quando esegui un’operazione DBCC SHRINKDATABASE con NOTRUNCATE seguita da un’operazione DBCC SHRINKDATABASE con TRUNCATEONLY.

Il database ristretto non deve essere necessariamente in modalità utente singolo. Altri utenti possono lavorare nel database quando viene ristretto, inclusi i database di sistema.

Non puoi restringere un database mentre è in corso il backup del database. Al contrario, non puoi fare il backup di un database mentre è in corso un’operazione di restringimento del database.

Come funziona DBCC SHRINKDATABASE

DBCC SHRINKDATABASE restringe i file di dati su base individuale, ma restringe i file di log come se tutti i file di log esistessero in un pool contiguo. I file vengono sempre rimpiccioliti dalla fine.

Assumiamo che abbiate un paio di file di log, un file di dati e un database chiamato mydb. I file di dati e di log sono 10 MB ciascuno e il file di dati contiene 6 MB di dati. Il Database Engine calcola una dimensione target per ogni file. Questo valore è la dimensione a cui il file deve essere ridotto. Quando DBCC SHRINKDATABASE è specificato con target_percent, il motore del database calcola la dimensione target per essere la quantità target_percentuale di spazio libero nel file dopo il restringimento.

Per esempio, se si specifica un target_percent di 25 per il restringimento di mydb, il motore del database calcola la dimensione target per il file dati per essere 8 MB (6 MB di dati più 2 MB di spazio libero). Come tale, il motore di database sposta qualsiasi dato dagli ultimi 2 MB del file di dati a qualsiasi spazio libero nei primi 8 MB del file di dati e poi restringe il file.

Assumiamo che il file di dati di mydb contenga 7 MB di dati. Specificando un target_percentuale di 30, questo file di dati sarà rimpicciolito alla percentuale libera di 30. Tuttavia, specificando un target_percent di 40 non restringe il file di dati perché il Database Engine non restringe un file a una dimensione più piccola di quella occupata dai dati al momento.

Puoi anche pensare a questo problema in un altro modo: Il 40 per cento di spazio libero desiderato + il 70 per cento di file dati pieno (7 MB su 10 MB) è più del 100 per cento. Qualsiasi target_size maggiore di 30 non ridurrà il file di dati. Non si ridurrà perché la percentuale di spazio libero che vuoi più la percentuale attuale che il file di dati occupa è più del 100 per cento.

Per i file di log, il Database Engine usa target_percent per calcolare la dimensione target per l’intero log. Ecco perché target_percent è la quantità di spazio libero nel log dopo l’operazione di restringimento. La dimensione target per l’intero log è quindi tradotta in una dimensione target per ogni file di log.

DBCC SHRINKDATABASE cerca di ridurre ogni file di log fisico alla sua dimensione target immediatamente. Diciamo che nessuna parte del log log log log logico rimane nei log virtuali oltre la dimensione target del file di log. Allora il file viene troncato con successo e DBCC SHRINKDATABASE termina senza alcun messaggio. Tuttavia, se parte del log log logico rimane nei log virtuali oltre la dimensione target, il Database Engine libera quanto più spazio possibile, e poi emette un messaggio informativo. Il messaggio descrive quali azioni sono necessarie per spostare il log log log fuori dai log virtuali alla fine del file. Dopo che le azioni sono state eseguite, DBCC SHRINKDATABASE può essere usato per liberare lo spazio rimanente.

Un file di log può essere ridotto solo al limite di un file di log virtuale. Ecco perché restringere un file di log a una dimensione inferiore a quella di un file di log virtuale potrebbe non essere possibile. Potrebbe non essere possibile anche se non viene utilizzato. La dimensione del file di log virtuale è scelta dinamicamente dal Database Engine quando i file di log sono creati o estesi.

Best Practices

Considera le seguenti informazioni quando pianifichi di restringere un database:

• Un’operazione di restringimento è più efficace dopo un’operazione che crea spazio inutilizzato, come un’operazione di truncate table o una drop table.
• La maggior parte dei database richiede un po’ di spazio libero per le regolari operazioni quotidiane. Potresti rimpicciolire un database ripetutamente e notare che la dimensione del database cresce di nuovo. Questa crescita indica che lo spazio ridotto è necessario per le operazioni regolari. In questi casi, restringere ripetutamente il database è un’operazione sprecata.
• Un’operazione di restringimento non conserva lo stato di frammentazione degli indici nel database, e generalmente aumenta la frammentazione in una certa misura. Questo risultato è un’altra ragione per non restringere ripetutamente il database.
• A meno che tu non abbia un requisito specifico, non impostare l’opzione di database AUTO_SHRINK su ON.

Risoluzione dei problemi

È possibile bloccare le operazioni di restringimento da una transazione che è in esecuzione sotto un livello di isolamento basato sul versioning delle righe. Per esempio, una grande operazione di cancellazione in esecuzione sotto un livello di isolamento basato sul versionamento delle righe è in corso quando viene eseguita un’operazione DBCC SHRINK DATABASE. Quando accade questa situazione, l’operazione di restringimento aspetterà che l’operazione di cancellazione sia completata prima di restringere i file. Quando l’operazione di restringimento aspetta, le operazioni DBCC SHRINKFILE e DBCC SHRINKDATABASE stampano un messaggio informativo (5202 per SHRINKDATABASE e 5203 per SHRINKFILE). Questo messaggio viene stampato nel log degli errori di SQL Server ogni cinque minuti nella prima ora e poi ogni ora successiva. Per esempio, se il log degli errori contiene il seguente messaggio di errore:

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 che hanno timestamp più vecchi di 109 bloccheranno l’operazione di shrink. Quella transazione è l’ultima transazione che l’operazione di restringimento ha completato. Indica anche che le colonne transaction_sequence_num o first_snapshot_sequence_num nella vista di gestione dinamica sys.dm_tran_active_snapshot_database_transactions (Transact-SQL) contengono un valore di 15. La colonna transaction_sequence_num o first_snapshot_sequence_num nella vista potrebbe contenere un numero inferiore all’ultima transazione completata da un’operazione di shrink (109). Se è così, l’operazione di restringimento aspetterà che quelle transazioni finiscano.

Per risolvere il problema, potete fare una delle seguenti operazioni:

• Fermare la transazione che sta bloccando l’operazione di restringimento.
• Fermare l’operazione di restringimento. Qualsiasi lavoro completato viene mantenuto.
• Non fare nulla e permettere all’operazione di restringimento di aspettare fino al completamento della transazione bloccante.

Permessi

Richiede l’appartenenza al ruolo fisso di server sysadmin o al ruolo fisso di database db_owner.

Esempi

A. Ridurre un database e specificare una percentuale di spazio libero

L’esempio seguente riduce la dimensione dei file di dati e di log nel database dell’utente UserDB per consentire il 10% di spazio libero nel database.

DBCC SHRINKDATABASE (UserDB, 10); GO 

B. Troncare un database

L’esempio seguente riduce i dati e i file di log nel database di esempio AdventureWorks all’ultima estensione assegnata.

DBCC SHRINKDATABASE (AdventureWorks2012, TRUNCATEONLY); 

C. Restringere un database Azure Synapse Analytics

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

Vedi anche

ALTER DATABASE (Transact-SQL)
DBCC (Transact-SQL)
DBCC SHRINKFILE (Transact-SQL)
Ridurre un database