Home > FAQ > Il transaction log e’ enorme e mi da problemi. Cosa posso fare?

Il transaction log e’ enorme e mi da problemi. Cosa posso fare?

Il transaction log viene principalmente utilizzato da SQL Server per garantire le proprietà ACID delle transazioni

In funzione del modello recupero del database (in inglese Recovery Model) il transaction log può essere utilizzato anche come strumento di backup in modo da poter ricostruire l’intero database nel caso qualcosa non andasse per il verso giusto.

SIMPLE RECOVERY MODEL

Se il modello di recupero è impostato a Semplice (Simple), il transaction log verrà utilizzato solo per garantire le proprietà ACID citate in precedenza. Ogni volta che una transazione viene confermata od annullata, lo spazio occupato dalla stessa nel transaction log verrà liberato automaticamente e ri-utilizzato.

Il transaction log può comunque trovarsi nella necessità di dover allocare più spazio su disco quando la dimensione totale delle transazioni che stanno avvenendo contemporanemente su SQL Server supera lo spazio libero sul transaction log. In questo caso, se l’opzione (Crescita Automatica) AutoGrow è abilitata il transaction log si espenderà automaticamente.

Nel modello di recupero simple, quindi, è raro che la dimensione del transaction log raggiunga dimensioni tali da essere problematiche. Se ciò accade è significa che sul vostro sistema c’è quache transazione che è rimasta aperta (quindi una situazione probabilmente di errore) e che impedisce il normale riutilizzo del transaction log.

E’ bene sottolineare che quando un database utilizza il modello di recupero Simple, in caso di crash di sistema che renda il database corrotto o di errore umano, l’unica possibilità di recupero dei dati è quella di effetture il restore dell’ultimo backup full o differenziale disponibile. Ciò significa che tutte le operazioni fatte dall’ora in cui è terminato l’ultimo backup disponibile fino al momento del crash o dell’errore sono perse. Valutate quindi  molto bene se utilizzare questo modello di recupero nei vostri database di produzione.

FULL RECOVERY MODEL

Nel modello di recupero Full il transaction log non libera lo spazio occupato da una transazione terminata fino a quando non viene fatto il backup del transaction log stesso utilizzando il comando BACKUP LOG. Se quindi l’opzione AutoGrow è abilitata per il file del log (e lo è di default), e non viene fatto il backup del log in modo peridico, il transaction log continuerà a crescere fino ad accupare tutto lo spazio disponibile.

Questo funzionamento del transaction log è importantissimo e vitale perchè non elimando le transazioni terminate dal proprio interno, permette di effettuare quella che si chiama Point in Time Recovery. Se il database in uso diventa inutilizzabile per un motivo qualsiasi (errore umano, crash di sistema, errore hardware) si può fare il restore dell’ultimo backup full o differenziale effettuato, e si può quindi procedere ad utilizzare i backup del transaction log ed il transaction log stesso per ripristinare tutte le operazioni effettuate dall’ultimo backup fino al momento in cui si è verificato il problema. In altre parole è possibile non perdere nulla del lavoro fino a quel momento svolto.

E’ per questo motivo che un database in modello di recupero Full DEVE avere un piano di manutenzione che prevede il backup del transaction log in modo costante e periodico, altrimenti prima o poi il transaction log arriverà ad occupare tutto lo spazio su disco.

RIDURRE IL TRANSACTION LOG

Se il log è cresciuto fino a raggiungere dimensioni problematiche è necessario ridurre le dimensioni a mano.

Per prima cosa è necessario svutare il log dalle transazioni completate che lo stanno riempendo. Con SQL Server 2000 o 2005 si deve usare il comando

BACKUP LOG <database> WITH TRUNCATE ONLY

Con SQL Server 2008 questo comando è deprecato e non più disponibile, ed è pertato necessario modificare il modello di recupero del database

ALTER DATABASE <database> SET RECOVERY SIMPLE

Fatto ciò il log è svuotato dalle transazioni completate, ma il file rimane della stessa grandezza. Questo è dovuto al fatto che SQL Server non restituisce mai al sistema operativo dello spazio disco acquisto, a meno che non lo si faccia a mano o si utilizzi l’opzione AutoShrink. Ciò avviene perchè l’aumento delle dimensioni di un file può essere costoso in termini di performance ed anche l’operazione di rimpicciolimento impatta sulle performance andando a frammentare i dati. Per questo motivo è fortemente sconsigliato – se non ci è in presenza di problemi legati allo spazio su disco – di utilizzare tale opzione.

Per diminuire a mano la dimensioni di un file, ed in particolare del file di log, si deve utilizzare il comando DBCC SHRINKFILE:

DBCC SHRINKFILE (‘<log file logical name’>, <target_size_in_mb>)

ad esempio:

DBCC SHRINKFILE (‘LogDemo_log’, 10)

Cosi facendo SQL Server ridimensionerà il log al valore più vicino alla dimensione richiesta. Non è detto che la dimensione richiesta sia precisamente raggiungibile – il motivo è da ricercarsi nell’architettura interna del transaction log – e quindi si avrà il file ridimensionato valore minimo più vicino a quello richiesto.

Fatto ciò – se siete su SQL Server 2008 – potete rimenttere il database in modello di recupero Full.

Ricordate d’ora in poi che se avete un database in modello di recupero Full dovete avere un piano di manutenzione che si preoccupi di fare il backup del transaction log.

 

Chi è Davide Mauri

Microsoft Data Platform MVP dal 2007, Davide Mauri si occupa di Data Architecture e Big Data nel mondo dell'IoT. Attualmente ricopre il ruolo di "Director Software Development & Cloud Infrastructure" in Sensoria, societa specializzata nella creazione di Wearables e sensori per l'IoT applicati a tessuti ed oggetti sportivi.

Leggi Anche

Usare json su SQLServer 2005, 2008, 2012 e 2014 .. è possibile!

Vedere il supporto nativo di JSON su SQLServer 2016 (e anche su Azure SQLDatabase) mi …