File log troppo grande

lunedì 05 maggio 2014 - 11.57

zseven Profilo | Senior Member

Ciao ragazzi,
sul server abbiano notato che il file di log di un database è arrivato alla cifra record di 18 giga!
Questo ci crea un po' di problemi anche solo per leggere le tabelle perché ha esaurito tutto lo spazio sul server.

Come possiamo per sistemare questa situazione?

Grazie mille

alx_81 Profilo | Guru

>Ciao ragazzi,
ciao

>sul server abbiano notato che il file di log di un database è
>arrivato alla cifra record di 18 giga!
>Questo ci crea un po' di problemi anche solo per leggere le tabelle
>perché ha esaurito tutto lo spazio sul server.
>Come possiamo per sistemare questa situazione?
situazione normale.. hai un piano di backup?
Se sì, com'è? Fai mai il backup del log delle transazioni?

>Grazie mille
di nulla!

Alessandro Alpi | SQL Server MVP
MCP|MCITP|MCTS|MCT

http://blogs.dotnethell.it/suxstellino
http://suxstellino.wordpress.com
http://mvp.microsoft.com/profiles/Alessandro.Alpi

zseven Profilo | Senior Member

Ciao Alx,
grazie per la risposta.

Dunque il backup lo eseguiamo in questa maniera, seguendo tra l'altro proprio indicazioni forse tue di qualche anno fa su questo stesso forum.
Ti scrivo il codice che eseguiamo ogni notte:

Ovviamente oltre questo backup facciamo anche il backup del master e del model.
Possiamo fare qualcosa per risolverE?
Grazie mille!

USE master; GO DECLARE @Device varchar(100); BEGIN TRY /* BACKUP DB AS400NW */ PRINT N'Backup database <nome db> in corso...'; SET @Device = 'C:\BACKUP\AS400NW_Full_' + CONVERT(char(10), CURRENT_TIMESTAMP, 120) + '.bak'; BACKUP DATABASE AS400NW TO DISK = @Device; PRINT N'Backup database <nome db> eseguito correttamente.'; END TRY BEGIN CATCH PRINT N''; PRINT N'Durante il backup si è verificato il seguente errore:'; PRINT N'Codice: ' + CAST(ERROR_NUMBER() AS nvarchar(10)); PRINT N'Descrizione: ' + ERROR_MESSAGE(); END CATCH

alx_81 Profilo | Guru

>Possiamo fare qualcosa per risolverE?
mi hai risposto col codice.. non fate backup del log delle transazioni, operazione necessaria se vuoi evitare che il log continui a crescere senza mai riusare lo spazio.
Dovrai usare il comando BACKUP LOG: http://technet.microsoft.com/en-us/library/ms179478.aspx#TsqlProcedure
Qui una panoramica sui backup del log delle transazioni: http://technet.microsoft.com/en-us/library/ms191429.aspx

Alessandro Alpi | SQL Server MVP
MCP|MCITP|MCTS|MCT

http://blogs.dotnethell.it/suxstellino
http://suxstellino.wordpress.com
http://mvp.microsoft.com/profiles/Alessandro.Alpi

zseven Profilo | Senior Member

ok, se ho capito bene quindi è sufficiente modificare il codice che ti ho scritto con l'aggiunta di queste righe

BACKUP DATABASE AS400NW
TO DISK = @Device;

BACKUP LOG AS400NW
TO DISK = @Device;

E' corretto?
Se questo che ho scritto è corretto, cosa avviene al file di log?
Si svuota ritornando ad un peso accettabile? Ed il file di backup log generato non è poi anche lui pesantissimo?

Grazie mille

alx_81 Profilo | Guru

>E' corretto?
direi di sì

>Se questo che ho scritto è corretto, cosa avviene al file di log?
la porzione del file di log che non avevi backuppato (occhio che nel tuo caso, la prima volta sono 18 giga) viene archiviata sul tuo disco come backup del log e il file di log viene liberato. Continua a tenere 18 giga, ma è vuoto. Ragion per cui puoi procedere con l'operazione di SHRINK, oppure ridimensionarlo direttamente per ottenere una dimensione più ragionevole. Quest'ultima dovrebbe corrispondere alla dimensione massima che il tuo log, opportunamente backuppato, raggiungerà, in modo da non fallo mai crescere (operazione pesante per il database).

Alessandro Alpi | SQL Server MVP
MCP|MCITP|MCTS|MCT

http://blogs.dotnethell.it/suxstellino
http://suxstellino.wordpress.com
http://mvp.microsoft.com/profiles/Alessandro.Alpi

zseven Profilo | Senior Member

Scusami ma non ho capito bene, l'operazione di Shrink la devo fare sul file backuppato o sul file di log che è diventato vuoto dopo il backup?

Adesso inoltre sul server non è più disponibile neanche un giga di spazio, quindi penso che questa operazione è per forza vincolata ad un aumento dello spazio fisico sul server, stando a quanto mi dici, e cioè che viene comunque creato un file di 18 giga come backup...

alx_81 Profilo | Guru

>Scusami ma non ho capito bene, l'operazione di Shrink la devo
>fare sul file backuppato o sul file di log che è diventato vuoto
>dopo il backup?
lo shrink (o compattamento) si fa dello spazio dovuto alla "frammentazione" del file di log. Frammentazione è tra virgolette perchè in realtà il file non è che sia frammentato fisicamente, ma, siccome l'operazione di backup su di esso ha segnato come da riusare le sezioni backuppate (tutte meno quelle che si creano dopo l'istante di lancio del backup), esse creano "spazio" vuoto virtuale. Fisicamente il file, per capirci, tiene sempre 18GB, ma realmente utilizzati da informazioni utili al database ci sarà solo qualche kb (o qualche mb, dipende dal traffico che hai). Lo shrink toglie quello spazio occupato. Ma è importante fare backup di log (come hai fatto tu modificando lo script) per evitare che la problematica si ripeta.
Il file di backup, è una fotografia di una situazione. Che puoi tranquillamente gettare nel cestino dopo che non hai più bisogno di fare restore nei punti precisi indietro nel tempo. Dopo un full, se vuoi, puoi buttare tutti i backup log precedenti ad esso.
Dipende da te e da quanto vuoi tenerli.
Mi raccomando, cerca di leggere la documentazione che ti ho passato, c'è scritto tutto questo.

>Adesso inoltre sul server non è più disponibile neanche un giga
>di spazio, quindi penso che questa operazione è per forza vincolata
>ad un aumento dello spazio fisico sul server, stando a quanto
>mi dici, e cioè che viene comunque creato un file di 18 giga
>come backup...
beh il backup lo puoi fare dove vuoi.. Tuttavia se il database è fermo, puoi seguire anche un'altra strada di emergenza. Cambiare il recovery model del database a simple (ignora il log, e quindi non serve più il backup del log) e poi effettuare l'operazione di shrink, dopodichè, tornare al recovery model full. Lo cambi nelle proprietà del db.
Alessandro Alpi | SQL Server MVP
MCP|MCITP|MCTS|MCT

http://blogs.dotnethell.it/suxstellino
http://suxstellino.wordpress.com
http://mvp.microsoft.com/profiles/Alessandro.Alpi

zseven Profilo | Senior Member

Ok, quindi ricapitolando se ho capito bene, considerando che adesso non h bisogno del file di log perché non c'è alcun problema, posso procedere così:
- prima dovrò per forza acquistare più spazio sul server per stare tranquillo durante queste operazioni. Visto che è un cloud posso aggiungere una 30ina di Giga e poi eliminarli subito dopo.
- successivamente faccio il bakcup del log
- fare lo shrink del file log (posso farlo da management tramite compatta database?)
- Cancellare il backup del file log

E' tutto giusto? ;)

Grazie!

alx_81 Profilo | Guru

>- prima dovrò per forza acquistare più spazio sul server per
>stare tranquillo durante queste operazioni. Visto che è un cloud
>posso aggiungere una 30ina di Giga e poi eliminarli subito dopo.
>- successivamente faccio il bakcup del log
>- fare lo shrink del file log (posso farlo da management tramite
>compatta database?)
>- Cancellare il backup del file log
>E' tutto giusto? ;)
guarda, siccome il backup serve per poter fare restore, e tu lo butti.. fatti un full, metti in simple, ridimensiona il log e riparti senza acquistare spazio.
Perchè se devi fare l'operazione di backup del log per buttarlo, allora svuotalo e non ci pensare.
Ricapitolando, opzione veloce:
- ALTER DATABASE NomeDb SET RECOVERY SIMPLE
- Resize del file di log
- ALTER DATABASE NomeDb SET RECOVERY FULL

opzione più corretta, quella che indichi tu esclusa la cancellazione del file di log e lo shrink (a regime non ne devi avere bisogno).

>Grazie!
di nulla!
Alessandro Alpi | SQL Server MVP
MCP|MCITP|MCTS|MCT

http://blogs.dotnethell.it/suxstellino
http://suxstellino.wordpress.com
http://mvp.microsoft.com/profiles/Alessandro.Alpi

zseven Profilo | Senior Member

se prima avevo capito quasi tutto adesso mi sono perso....

Tu mi scrivi:

- ALTER DATABASE NomeDb SET RECOVERY SIMPLE
a cosa serve?

- Resize del file di log
Sarebbe il compatta database che ti ho detto prima, giusto?

- ALTER DATABASE NomeDb SET RECOVERY FULL
A che cosa serve?

Questi Alter Database compromettono le funzionalità del sito che lavora su questo db?
Soprattutto rispetto all'automatismo che ho impostato per il backup che ti ho scritto nel primo messaggio, vorrei capire bene cosa vado a fare.
Scusami ma ho parecchie falle in questa gestione del database.

Grazie mille!

alx_81 Profilo | Guru

>se prima avevo capito quasi tutto adesso mi sono perso....
mi spiace tanto.. forse ho sbagliato a darti due soluzioni possibili, però, veramente, leggi con attenzione i link che ti ho passato. Vedrai che ti fa anche gli esempi su come gestire i backup, le linee del tempo, ecc.. Non sono argomenti da prendere alla leggera e a mio avviso, documentarsi non è mai abbastanza.

>- ALTER DATABASE NomeDb SET RECOVERY SIMPLE
>a cosa serve?
serve a mettere il recovery model del tuo database a simple, ovvero serve a dire al motore di database di utilizzare il log delle transazioni SOLO per il lavoro corrente, ignorando la necessità di mantenere la storia di tutte le operazioni. Il log in quel caso serve solo da file di appoggio per la transazionalità delle operazioni a database.
Da come sembra, il tuo database (visto che il log cresce a dismisura) è in full o bulk-logged (che sono gli altri due modelli). Ciò significa che il file di log viene usato sia per la transazionalità, sia per mantenere la storia dell'accaduto sul tuo database. TUTTA la storia, fino a che non si effettua un BACKUP DEL LOG (da non confondere con un FULL BACKUP che fa solo l'unione dei file mdf/ndf con la parte di log ancora non portata su quei file). Da quanto vedo, tu hai fatto solo tanti full ed è per questo che il log, mai backuppato, è cresciuto di continuo. Vi è da dire che l'operazione di crescità sarà pure costata tanto al tuo database, trattandosi di un'operazione su disco molto pesante.
Una buona pratica, per il futuro, è dare una dimensione che non cresca mai per il log, un "polmone", che corrisponde alla massima dimensione che si raggiunge tra un backup del log e l'altro, nei giorni di maggior traffico.

>- Resize del file di log
>Sarebbe il compatta database che ti ho detto prima, giusto?
dopo che hai cambiato il recovery model puoi anche proprio andare a cambiare direttamente le dimensioni del file da designer (proprietà del database). Ma dietro le quinte, se riesce, sql server lancia la SHRINK (o compatta come di ci tu).

>- ALTER DATABASE NomeDb SET RECOVERY FULL
>A che cosa serve?
torna al tuo modello di recupero (sinonimo di recovery model), che ho ipotizzato fosse full, ma lo vedi sempre dalle proprietà del database.

>Questi Alter Database compromettono le funzionalità del sito che lavora su questo db?
guarda, se sei completamente bloccato, il mio consiglio è di dare down per un po'.. tanto da quel che ho capito sei offline perché è tutto bloccato.
Se così non fosse, quello che accade è che viene dimenticato il log delle transazioni.. e quindi, in caso di danno, non puoi tornare al momento specifico in cui è avvenuto il misfatto. Alla fine il modello di recupero FULL serve proprio per poter fare backup di log (in simple non puoi farli) per, di conseguenza, poter fare restore nel punto perfetto in cui hai avuto il problema.

>Scusami ma ho parecchie falle in questa gestione del database.
capisco perfettamente, sono cose molto impegnative, ma allo stesso tempo, nemmeno poi così difficili.
Scusa se insisto, ma approfondisci l'argomento. Da un post di un forum deriva un aiuto, ma pratica e studio sono sempre vincenti.

Alessandro Alpi | SQL Server MVP
MCP|MCITP|MCTS|MCT

http://blogs.dotnethell.it/suxstellino
http://suxstellino.wordpress.com
http://mvp.microsoft.com/profiles/Alessandro.Alpi

zseven Profilo | Senior Member

Perfetto, sei stato molto chiaro.

Adesso ho solo letto tutto quanto hai scritto, prima di risponderti voglio approfondire i link che mi hai dato così ho qualche nozione in più.

Di sicuro posso dirti che nonostante questo file così pesante, e nessuno spazio sul server il sito continua ugualmente a funzionare senza nessun problema.

A prestissimo!
GRAZIE!!

alx_81 Profilo | Guru

>Perfetto, sei stato molto chiaro.
>GRAZIE!!
se ritieni che il post ti abbia aiutato, e se ti ha risolto il problema, non dimenticarti di accettare la risposta così lo chiudiamo..

ti aggiungo anche questo interessante link: http://www.sqlshack.com/10-important-sql-server-transaction-log-myths/?WT.mc_id=Social_TW_OutgoingEducation_20140505_56570371_SQLServer&linkId=8145528

ciao e grazie a te

Alessandro Alpi | SQL Server MVP
MCP|MCITP|MCTS|MCT

http://blogs.dotnethell.it/suxstellino
http://suxstellino.wordpress.com
http://mvp.microsoft.com/profiles/Alessandro.Alpi

zseven Profilo | Senior Member

Ciao Alx,
ancora non sono riuscito ad effettuare le operazioni sul db per altri problemi sorti, ma inizio a vistare la risposta come risolto!

Grazie!

zseven Profilo | Senior Member

Ok tutto fatto ed è andato bene, giusto per capire meglio vorrei farti qualche altra domanda.

Se ho capito bene per poter modificare la dimensione del file log il db deve stare per forza in stato di Recovery SIMPLE perché altrimenti i Log sono "lockati" per così dire al db e quindi non può essere ridotto il file.
Ma dopo quale è il motivo per cui ho rimesso il db a FULL? Solo per un fatto di precauzione? Cioè è sempre meglio che stia a full così ho tutte le log backappate e poi magari ogni tanto faccio questa pulizia?

Grazie mille sempre

alx_81 Profilo | Guru

>Se ho capito bene per poter modificare la dimensione del file
>log il db deve stare per forza in stato di Recovery SIMPLE perché
>altrimenti i Log sono "lockati" per così dire al db e quindi
>non può essere ridotto il file.
fino a che non fai il backup del log, operazione dopo la quale, puoi fare l'operazione di shrink e resize

>Ma dopo quale è il motivo per cui ho rimesso il db a FULL? Solo
>per un fatto di precauzione? Cioè è sempre meglio che stia a
>full così ho tutte le log backappate e poi magari ogni tanto
>faccio questa pulizia?
A dire il vero dovresti chiederti perchè è a full. Il recovery model serve per darti una protezione sull'eventuale perdita di dato.
Quindi, potresti scoprire che il simple va più che bene.. Quali sono le tue esigenze di restore in caso di danno?
Si parte da questo..
Alessandro Alpi | SQL Server MVP
MCP|MCITP|MCTS|MCT

http://blogs.dotnethell.it/suxstellino
http://suxstellino.wordpress.com
http://mvp.microsoft.com/profiles/Alessandro.Alpi

zseven Profilo | Senior Member

Beh sicuramente trattandosi di un e-commerce poter recuperare tutte le info fino al momento del blocco è preferibile, altrimenti se dovesse succedere qualcosa sarei costretto a recuperare le info fino alla notte appena trascorsa, momento in cui faccio il backup.

alx_81 Profilo | Guru

>Beh sicuramente trattandosi di un e-commerce poter recuperare
>tutte le info fino al momento del blocco è preferibile, altrimenti
>se dovesse succedere qualcosa sarei costretto a recuperare le
>info fino alla notte appena trascorsa, momento in cui faccio il backup.
allora ti serve il FULL, recovery model che ti consente di fare backup dei LOG ogni X tempo.
Disegna un bel piano di backup fatto ad esempio come segue:
- full giornaliero
- diff ogni 3 ore
- log ogni mezz'ora

in questo modo sei ben coperto e il log non cresce più di tanto, visto che il bacup del log archivia le informazioni precedentemente salvate sul ldf.

Alessandro Alpi | SQL Server MVP
MCP|MCITP|MCTS|MCT

http://blogs.dotnethell.it/suxstellino
http://suxstellino.wordpress.com
http://mvp.microsoft.com/profiles/Alessandro.Alpi
Partecipa anche tu! Registrati!
Hai bisogno di aiuto ?
Perchè non ti registri subito?

Dopo esserti registrato potrai chiedere
aiuto sul nostro Forum oppure aiutare gli altri

Consulta le Stanze disponibili.

Registrati ora !
Copyright © dotNetHell.it 2002-2025
Running on Windows Server 2008 R2 Standard, SQL Server 2012 & ASP.NET 3.5