Autoshrink in SQL Server

Autoshrink in SQL Server 2017

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.

Attivare Autoshrink in SQL Server - Proprietà Database
Attivare Autoshrink in SQL Server – Proprietà 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
GO

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

Autore Bartolomeo

Consulente tecnico con la passione per il web e la tecnologia e tutto quello che le ronza intorno.
Nel tempo libero scrivo articoli per il mio blog su argomenti vari, in particolare su configurazione Windows, Linux e WordPress.

Condividi questo articolo!

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *

Ho letto la policy privacy e accetto il trattamento dei miei dati personali in conformità al D.Lgs. 196/2003