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.

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.
SQL back-up
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.
SQL back-up
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.