4 Údržba SQL dat

Owned by Tomas Stepanek
Last updated: čvc 25, 2024 by Linda Opluschilová
4 min read

Zálohování transakčního logu

Data jsou v SQL serverech uložena do několika souborů. Ten nejčastější scénář je uložení dat do souboru s příponou .mdf a uložení transakčního logu do souboru s příponou .ldf

V MDF souboru jsou čistě data. V LDF je právě zmiňovaný transakční log, který během práce s programem může měnit svou velikost až do několikanásobné velikosti (např. několik desítek GB). Pravidelnou zálohou transakčního logu (TRN) se jeho velikost zmenšuje, nezabírá tak  disk, práce s daty je tak rychlejší. Jak zálohovat tento TRN si ukážeme níže. Možností je více, ale jedna je přímo ve vlastnostech konkrétní databáze.



V SQL Studiu, pravým tlačítkem klepneme na naší databázi, z menu zvolíme "Properties" a v pravé části dialogu je možnost Transaction LOG. Zde je třeba nastavit cíl, kam se budou záložní soubory ukládat. Je nutné nastavit oba způsoby, tedy jak lokální úložiště, tak úložiště pomocí UNC cesty (oba cíle mohou vést na jedno místo). Platnost jedné zálohy nestavíme na jeden den, protože po plné záloze již TRN potřebovat nebudeme. Z těchto záloh TRN je možné v případě potřeby obnovit celou databázi nebo jen předchozí stav, např před 30 minutami. Níže na obrázku je příklad nastavení, kde se záloha bude ukládat každých 30 minut.



Interval zálohy TRN je na zvážení. Pokud v systému pracuje více uživatelů (více než 10), je pravděpodobné, že při jejich práci bude docházet k většímu počtu přístupů k datům a tím se bude TRN zvětšovat. Doporučená hodnota je každých 30 minut. Pokud se bude TRN zálohovat často, jako např. zmiňovaných 30 minut, nebude to nijak zdržovat server a záložní soubory nebudou příliš velké. Naopak, pokud se bude záloha TRN provádět například co 2 hodiny a v systému je běžně přihlášeno 15 lidí bude jedna záloha trvat delší dobu, bude se zatěžovat více server a záložní TRN soubory budou větší. Při intervalu 30 minut, je doba vytvoření takové zálohy od 1 do 3 minut.

Zálohu TRN nelze spustit na databázi, na které ještě neproběhla plná záloha, proto je nutné nejdříve vytvořit aspoň jednu plnou zálohu



Z důvodu možného výpadku služby SQL Agent nebo zálohování TRN není nastaveno vůbec se může stát, že soubor s transakčním logem (.ldf) naroste do obrovských rozměrů a bude hrozit, že dojde místo na disku serveru, popřípadě už došlo. V takovém případě trvá velmi dlouho, než by se standardním způsobem zmenšoval a tak se tato záležitost může vyřešit rychle a efektivně.

Provedeme to ve dvou krocích.

1. Znovu ve vlastnostech konkrétní databáze přejdeme do položky "Options" a nastavíme Recovery model na volbu "Simple". Uložíme OK

2. Opět vstoupíme do vlastností databáze a přejdeme do položky "Files". Zde nastavíme počáteční velikost pro LDF soubor na 50 MB



Opět uložíme - OK. SQL server nyní začne upravovat velikost transakčního logu - souboru LDF. Tato operace může trvat několik minut v závislosti na výkonu SQL serveru a volné paměti RAM. Po ukončení bude LDF soubor na velikosti max. 50MB. Jakmile máme problém s obrovským LDF souborem vyřešen, je nutné přepnout zpět položku Recovery Model na volbu "Full" a ihned také nastavit zálohování TRN jak je uvedené výše a provést plnou zálohu databáze, jinak nemůže záloha TRN probíhat.

Ukázka možného základního plánu pro údržbu dat v SQL serveru

Níže je uveden návrh na možný plán údržby dat v SQL serveru. Plán je tvořen z pěti základních úloh.

  1. Záoha TRN - popsáno výše

  2. Záloha DB - Plná, denní záloha SQL dat. Pravidelnost 1x denně

  3. Shrink DB - Optimalizace databáze. Pravidelnost 1x denně

  4. Reorganize index - Defragmentuje a třídí indexy v databázích a zajišťuje tak optimální velikost a fragmentaci indexů. Je vhodné naplánovat 1x týdně ve dnech pracovního klidu či v nočních hodinách

  5. Clean - Odstranění a vyčištění historie plánovače. Pravidelnost 1x týdně a je možné zvolit jak staré záznamy se budou z historie odstraňovat.