Home Page Home Page Tips & Tricks Come faccio a ridurre le dimensioni del Transaction Log in SQL Server 2000?

Come faccio a ridurre le dimensioni del Transaction Log in SQL Server 2000?


Il Transaction Log registra tutte le modifiche effettuate in un database ed una serie di informazioni aggiuntive che gli permettono di annullare (roll back) o ripristinare (roll forward) tali modifiche in caso di un errore di sistema (Hardware o Software) oppure a causa di un comando esplicito di ROLLBACK della transazione garantendo in questo modo la consistenza dei dati.
Dal punto di vista fisico il Transaction Log è costituito da uno o più files associati ad un database durante la sua creazione/modifica.

Vediamo cosa succede quando effettuiamo una modifica ad una tabella.

SQL Server memorizza le modifiche nel Transaction Log su disco nel momento in cui tali modifiche sono eseguite, prima che vengano memorizzate nel database.

Il processo può essere brevemente riassunto nei seguenti punti:

Una applicazione invia una modifica a SQL Server; Non appena la modifica viene eseguita, le data pages coinvolte vengono lette da disco ed inserite nel buffer cache (nel caso non siano già presenti a causa di un precedente comando); Ogni istruzione di modifica viene registrata nel log non appena viene eseguita. Le modifiche sono sempre registrate nel log e scritte su disco prima che vengano inserite nel database.
Questo tipo di log viene chiamato write-ahead;
Ciclicamente si verifica il processo di checkpoint che scrive fisicamente nel database tutte le transazioni completate.
Se si verifica un problema (schianta un disco, va via la corrente e come al solito l'elettricista si è dimenticato di mettere sotto UPS i server, prende fuoco la sala fredda... adesso sto esagerando ;-)) all'accensione successiva parte automaticamente un processo di recovery che utilizza il transaction log per effettuare il roll forward di tutte le transazioni concluse correttamente (committed) e l'annullamento (roll back) di quelle non ancora concluse.

I transaction markers nel log sono utilizzati durante il processo automatico di recovery per determinare i punti di partenza e di fine di una transazione. Una transazione è considerata completata quanto il marker BEGIN TRANSACTION ha il corrispondente marker COMMIT TRANSACTION. Le data pages sono scritte su disco quando si verifica il processo di checkpoint.

Per rimuovere dal transaction log le transazioni scritte nel database durante il checkpoint, puoi utilizzare il comando T-SQL BACKUP LOG. Se non ti interessa creare un file di backup, puoi specificare le opzioni WITH NO_LOG oppure TRUNCATE_ONLY (sono sinonimi).

Questa operazione non rilascia lo spazio allocato dal file(s) .ldf al file system. Per liberare spazio effettua uno shrink del file di log mediante il comando DBCC SHRINKFILE ().

Inoltre ti suggerisco di valutare quale recovery model è più adatto alle tue esigenze.

In SQL Server 2000 puoi selezionare per ogni database 3 recovery model in modo da determinare la modalità di backup dei dati e di stabilire l'esposizione al rischo di perdita di dati:

- Simple Recovery
Con il Simple Recovery Model, il database può essere recuperato in base ai dati presenti nell'ultimo backup effettuato. Comunque con questa impostazione non è possibile ripristinare il database al momento del crash o ad un determinato periodo temporale, in quanto le transazioni vengono eliminate dal Transaction Log ad ogni checkpoint. Per fare questo occorre selezionare il Full Recovery o Bulk-Logged Recovery Model.

- Full Recovery
Il Full Recovery Model utilizza i database backup ed i transaction log backup per fornire una protezione completa sulla perdita di dati. Se uno o più data file viene danneggiato, è possibile recuperare tutte le transazioni concluse con successo. Le transazioni in corso durante il crash ma non ancora concluse, saranno annullate.
Il Full Recovery permette di ripristinare il database al momento del crash o ad uno specifico periodo temporale. Per garatire questo grado di recovery, tutte le operazioni, incluse quelle bulk come la SELECT INTO, CREATE INDEX e operazioni di bulk loading sono loggate completamente.

- Bulk-Logged Recovery
Il Bulk-Logged Recovery Model fornisce protezione dei dati garantendo elevate prestazioni ed una bassa occupazione del transaction log durante le operazioni di bulk copy.
Le seguenti operazioni sono minimamente loggate:

- SELECT INTO
- Operazioni di Bulk load (bcp e BULK INSERT)
- CREATE INDEX (anche sulle indexed views)
- Operazioni sui campi text e image (WRITETEXT e UPDATETEXT)

Con il Bulk-Logged Recovery Model l'esposizione alla perdita di dati durante queste operazioni di bulk copy è maggiore rispetto al Full Recovery Model. Mentre le operazioni bulk copy sono loggate completamente nel caso di Full Recovery Model, lo sono minimamente e non possono essere controllare a livello di singola operazione impostando il Bulk-Logged Recovery Model.

In caso di guasto, un data file danneggiato può comportare un intervento manuale per ripristinare l'integrità dei dati.

Per ulteriori informazioni consulta sui Books Online i seguenti paragrafi:

http://msdn.microsoft.com/library/en-us/architec/8_ar_da2_7vaf.asp 
http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_03_6ohf.asp 
http://msdn.microsoft.com/library/en-us/tsqlref/ts_dbcc_8b51.asp 
Copyright © dotNetHell.it 2002-2018
Running on Windows Server 2008 R2 Standard, SQL Server 2012 & ASP.NET 3.5