Waarschijnlijk is het de meest gemaakte fout bij een back-up strategie voor je MS SQL Server. Veel mensen lopen namelijk tegen het probleem aan dat de transactionlog steeds groter en groter wordt. Uiteindelijk zal deze logfile blijven groeien tot de harde schijf vol is en.. Je SQL Server uit de lucht knalt. Gelukkig is dit in de meeste gevallen eenvoudig te voorkomen.

Back up Types
Zoals hierboven aangegeven heeft dit waarschijnlijk te maken met het instellen van je back-ups. Laten we eerst kort bespreken welke vormen van back-ups we eigenlijk kennen.

• Full-Back-up: de full back-up maakt eigenlijk gewoon een kopie van de datafile (.mdf).
• Differential-Back-up: deze back-up maakt een kopie van de gewijzigde data sinds de laatste full-back-up (dus niet de laatste differential-back-up!).
• Transactionlog-Back-up: in de transactionlog (.ldf) worden alleen transactions (zeg maar SQL-statements) opgeslagen. Niet de data dus. Tijdens een back-up worden deze statements opgeslagen; inclusief checkpoints die aangeven op welk momenten de transacties hebben plaats gevonden.

Recovery Models
Per database kunnen we een recovery model kiezen. Het recovery model is bepalen voor welke van de bovenstaande back-ups we kunnen en/of moeten maken.

We gaan kijken naar de 2 belangrijkste scenario’s:

• Simple recovery model: het “simple” recovery model wil zeggen dat je Full- en Differential-back-ups kunt maken, maar géén transactionlog-back-ups. Je data wordt dus bewaart en als je een back-up wilt terugzetten kun je dus terug naar het moment van je laatste Full- (of Differential) back-up. Aangezien er geen mogelijkheid is om de transactionlog te back-uppen, wordt bij elke Full back-up de logfile leeggemaakt. De ruimte op de harde schijf wordt echter niet vrijgegeven, dit om fragmentatie te voorkomen. De lege logfile wordt weer van voor af aan gevuld met nieuwe transacties en zal dus niet groeien (tenzij er meer transacties komen natuurlijk).
• Full: bij het “full” recovery model kunnen we ook transactionlog-back-ups maken. De full-back-up zal de logfile niet meer leeggooien, aangezien er dan transacties weggegooid zouden worden die nog niet in een transactionlog-back-up zitten. Wanneer je een transactionlog-back-up maakt, dan wordt de logfile weer leeg gemaakt. Als je een back-up wilt terugzetten, dan kun je naast de Full-back-up ook transactionlog-back-ups terugzetten. Aangezien bij de transacties dus ook tijden staan, kun je feitelijk terugkeren naar elk willekeurig tijdstip. Wil je dus terug naar 2 voor 9 vanochtend, omdat je weet dat het om 9 uur stuk ging, geen probleem!

Waar gaat het fout..
Je ziet dus dat de logfile op verschillende momenten leeg wordt gemaakt bij de 2 recovery models. Bij “Simple”-recoverymodel gebeurt het dus bij de Full back-up, bij het “Full”-receovery model gebeurt het dus alleen bij de transactionlog back-up. En hier gaat het vaak fout! Als je dus kiest voor “Full”-recoverymodel, MOET je dus transactionlog-back-ups maken. Zo niet, dan zal de logfile blijven groeien!

Mijn inziens wordt dit in de hand gewerkt door de verwarrende naamgeving. Je kiest voor “Full” recovery model (want dat klinkt toch een stuk beter dan “simple”) en vervolgens maak je netjes “Full”-back-ups en klaar is Kees. Nee dus.

De oplossing
Om dus te voorkomen dat je transaction-logfile gigantisch groeit, heb je eigenlijk 2 voor de handliggende opties:

1. Of je kiest voor het recovery-model “Simple” als je niet naar een specifiek moment in de tijd terug hoeft te kunnen, maar terug naar je laatste Full-back-up voldoende is.
2. Mocht dat wel een eis zijn, dan kies je voor recovery-model “Full” en maak je tussen de Full-back-ups ook 1 of meerdere Tansactionlog-back-ups.

In bovenstaande tekening zie je zo’n voorbeeld. Elke nacht om 12 uur wordt er een full-back-up gemaakt. En dan tussendoor een aantal transactionlog-back-ups. In dit geval om 9, 12 en 3 uur. Dit kan ook vaker of juist minder vaak, afhankelijk natuurlijk van je wensen en vereisten.

Tweet

Reacties (3) op "SQL back-up strategie: waarom blijft de transactionlog groeien?"

Ralph Droesen zegt op 28 maart 2012 @ 11:59:18

Hoi,

Is het mogelijk om een backup van de SQL database of de transactions log te maken als deze in gebruik is( onder werktijd ) of wordt dit afgeraden.
Wordt de database ofline gezet voor de backup of wordt het trager.
Later zal de backup wel ingesteld worden buiten werktijd.

met vriendelijke groet,
Ralph Droesen
ISL Automatisering

Christian Peeters zegt op 28 maart 2012 @ 16:08:16

Beste Ralph,

Het kan gewoon online, maar waarschijnlijk zul je inderdaad wel perdormance vermindering zien. Dus als je een goede reden hebt kan het overdag, maar in zou aanraden om het tijdens off-peak-hours te doen. Met een maintenanceplan is zoiets makkelijk te regelen.

Groeten Christian

Eric Jonk zegt op 16 mei 2012 @ 15:08:48

Een maintenance plan is wel erg ouderwets en misschien leuk voor kleine hoeveelheden servers en/of databases, maar grotere omgevingen is niet meer te doen.
Gebruik liever een eigen inrichting van de backup strategie, daarmee kunnen ook nieuw geplaatste database automatisch meegenomen worden in de backup en dat gaat niet met een maintenance plan.

Performance vermindering tijdens een online backup, de enige optie want offline kun je geen backup maken, is minimaal te noemen.
Zeker als er elk uur of zelfs elk kwartier een log backup wordt gemaakt.
Hangt ook af van het aantal transacties cq wijzigingen op de database gedurende die periode.
Elke dag een full backup is meestal zonde van schijfruimte, 1 keer in de week en elke avond een diff is meer dan voldoende.

Plaats je reactie

Populaire berichten

Navigatie

Blog categorieën