Autoshrink in SQL Server, vediamo insieme come attivare la funzionalità che consente a SQL Server di compattare automaticamente i file di database.
Uno dei motivi per cui si abilita l’opzione AUTO_SHRINK è quella di cercare di recuperare dello spazio inutilizzato nel database e quindi garantire maggiore spazio libero sul disco. Ci sono però degli effetti collaterali che spesso superano i benefici attesi.
La funzionalità di Autoshrink in SQL Server è disabilitata per impostazione predefinita nei database dell’istanza di SQL Server. Vediamo come attivarla.
Cos’è l’AutoShrink
AUTO_SHRINK è una opzione a livello di singolo database. Quando viene abilitata, il database diventa oggetto, attraverso un task in background, dell’operazione di compattazione.
Il task valuta periodicamente tutti i database che hanno questa opzione attivata ed esegue l’operazione di compattazione dei rispettivi file dati e/o dei transaction log (nel caso dei log, la compattazione avviene solo se il recovery model del database è impostato a SIMPLE oppure se è stato fatto un backup del log).
Lo shrink avviene solo se lo spazio inutilizzato all’interno del file è superiore al 25%. La dimensione viene conseguentemente ridotta del 25% senza superare il limite definito dalla dimensione del file al momento della sua creazione. Non possono essere compattati database di tipo read-only.
Come attivare Autoshrink in SQL Server
E’ possibile attivare l’ Autoshrink del database usando SSMS e T-SQL in entrambi i modi.
Attivazione Autoshrink tramite SSMS
Possiamo abilitare o disabilitare questa opzione dalle proprietà del database, con un tag di compattazione automatica. In questo caso, True nell’elenco a discesa abiliterà questa opzione per il database.
Abilitare Autoshrink database tramite T-SQL
Possiamo eseguire le istruzioni T-SQL per abilitare o disabilitare l’ Autoshrin. È stato utilizzato il database AdventureWorks per l’istruzione T-SQL
--Enable Auto Shrink for the database AdventureWorks
ALTER DATABASE AdventureWorks SET AUTO_SHRINK ON
GO
--Disable Auto Shrink for the database AdventureWorks
ALTER DATABASE AdventureWorks SET AUTO_SHRINK OFF
GOQuando utilizzare l’Autoshrink
Tele funzionalità viene abilitata quando abbiamo in un’istanza Sql Server N database di piccola dimensione.
Questo permette che quando vengono effettuate delle operazioni di inserimento di una grande quantità di dati possiamo recuperare subito spazio su disco.
Svantaggi Autoshrink
Occorre prestare molta attenzione quando si attiva l’ autoshrink in SQL Server. Frequenti operazioni di compattazione e riallocazione di spazio possono condurre a diversi problemi di performance:
- La compattazione del database è il modo più veloce per generare frammentazione. Le pagine dati del database vengono spostate partendo dalla fine del file al primo spazio libero disponibile al suo interno e questa operazione viene ripetuta finché non vengono riempiti tutti i buchi. Questo crea disordine, nonostante sembri il contrario, all’interno della struttura delle pagine dati. Se questo processo è poi combinato con la ricostruzione giornaliera degli indici, operazione che allarga lo spazio allocato nel database durante il processo di rebuild o riorganizzazione, ci rendiamo conto che ci mettiamo nella classica situazione del cane che si morde la coda: compatta, allarga, compatta, allarga… inutile.
- Dopo questa operazione, le operazioni DML o DDL che richiedono una riallocazione di spazio del database, potrebbero rallentare significativamente per consentire al sistema operativo di allocare lo spazio necessario su file system.
- Il task in background che si occupa della compattazione potrebbe consumare un quantitativo di risorse importante (CPU e disco) se questa operazione fosse richiesta frequentemente e per numerosi database.
- L’operazione richiede anche di acquisire dei lock sul database che potrebbero interferire con le normali attività di accesso ai dati da parte degli utenti.
Conclusione
Abbiamo visto come attivare l’autoshrink in Sql Server. Prima di abilitare tale funzionalità occorre effettuare un attenta valutazione sull’ambiente di produzione sulla quale è installata l’istanza Sql Server.
Come alternativa puoi monitorare l’utilizzo del disco utilizzando i report messi a disposizione in SSMS che fornisce informazioni dettagliate sui dati e sullo spazio di log per un database .
