MSSQL transakčné logy: Neviditeľný požierač diskového priestoru

Transakčné logy v SQL Serveri sú kľúčovým prvkom databázového systému, no ich význam a správa býva často podceňovaná. Každá databáza v SQL Serveri má svoj logický záznamový súbor (s príponou .ldf), ktorý uchováva informácie o všetkých zmenách vykonaných nad databázou vrátane vkladania, úprav a mazania údajov. Tieto logy slúžia primárne na zabezpečenie konzistencie dát a umožňujú tzv. „point-in-time recovery“, teda obnovenie databázy do konkrétneho časového bodu v prípade havárie.

Znie to užitočne a aj je. Problém nastáva vtedy, keď tieto logy nikto nezálohuje, nesleduje alebo ich neobmedzí. Bez údržby a správneho nastavenia recovery modelu môže log narastať prakticky donekonečna. V produkčných prostrediach, kde bežia veľké importy, ETL úlohy alebo hromadné operácie, sa veľkosť transakčného logu môže vyšplhať na stovky gigabajtov, ba dokonca niekoľko terabajtov, čím zahlcuje diskový priestor a znižuje výkon systému.

Našťastie existujú riešenia. SQL Server ponúka rôzne režimy obnovy (FULL, SIMPLE, BULK_LOGGED), z ktorých každý má vplyv na správanie transakčných logov. V prípade, že nevyužívate možnosť časového obnovenia databázy, môžete logovanie obmedziť prechodom do SIMPLE recovery modelu. Alternatívne, ak zostanete pri FULL, stačí pravidelne zálohovať logy čím sa ich veľkosť prirodzene obmedzí.

Recovery modely v SQL Serveri: Čo určujú a prečo na nich záleží

SQL Server podporuje tri hlavné recovery modely: FULL, SIMPLE a BULK_LOGGED. Tieto režimy určujú, ako SQL Server zaznamenáva transakcie do logov a aké možnosti obnovy databázy poskytuje.

1. FULL recovery model

Tento režim uchováva každú transakciu až dovtedy, kým nie je vykonaná záloha logu. Poskytuje najvyššiu úroveň ochrany dát umožňuje obnoviť databázu na presný časový bod. Je nevyhnutný v produkčných prostrediach, kde je kritická kontinuita prevádzky.

Nevýhoda:
Ak sa pravidelne nezálohujú transakčné logy, .ldf súbor rastie donekonečna, čo vedie k zahlteniu disku.

2. SIMPLE recovery model

Transakčné logy sú priebežne orezávané po skončení transakcie a checkpointu sa uvoľní ich miesto. Obnova databázy je možná len na posledný plný backup, nie na konkrétny čas.

Výhoda:
Nepotrebuješ zálohovať logy, logy nezaberajú veľa miesta.
Nevýhoda:
Nie je možná „point-in-time“ obnova.

3. BULK_LOGGED recovery model

Ide o kompromis medzi FULL a SIMPLE. Pri hromadných operáciách (napr. BULK INSERT, SELECT INTO) sa používa minimalizované logovanie. Môže sa použiť pre výkon počas veľkých dávok, ale stále vyžaduje zálohovanie logov.

---

🧠 Ktorý recovery model použiť?

Scenár	Odporúčaný recovery model
Produkčný systém s kritickými dátami	FULL
Vývojové/testovacie prostredie	SIMPLE
ETL server počas dávkového spracovania	BULK_LOGGED alebo SIMPLE
Reportingové databázy	SIMPLE

Keď logy prerastú systém: praktický príklad:

Riešenie: ako zmenšiť logy a zabrániť ich rastu

Pred akoukoľvek zmenou je dobré mať prehľad o tom, v akom režime sú vaše databázy. Spusť si tento jednoduchý dotaz:

SELECT name AS Databaza, recovery_model_desc AS Rezim
FROM sys.databases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb');

Automatické riešenie: Skript na prepnutie recovery režimu a zmenšenie logu

Tento skript:

• Nájde log súbor a pokúsi sa ho zmenšiť na 100 MB.

• Preskočí systémové databázy.

• Pre každú databázu nastaví SIMPLE recovery režim.

DECLARE @dbname NVARCHAR(128)
DECLARE @logfile NVARCHAR(128)
DECLARE @sql NVARCHAR(MAX)

DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT '---- Spracovavam databazu: ' + @dbname

    -- Nastavenie SIMPLE recovery modelu
    SET @sql = 'ALTER DATABASE [' + @dbname + '] SET RECOVERY SIMPLE WITH NO_WAIT'
    EXEC (@sql)

    -- Ziskanie nazvu LOG suboru
    SET @sql = '
        SELECT @logfile_out = name 
        FROM [' + @dbname + '].sys.database_files 
        WHERE type_desc = ''LOG'''
    
    EXEC sp_executesql @sql, N'@logfile_out NVARCHAR(128) OUTPUT', @logfile_out=@logfile OUTPUT

    IF @logfile IS NOT NULL
    BEGIN
        PRINT '  → Zmensujem logovy subor: ' + @logfile

        SET @sql = 'USE [' + @dbname + ']; DBCC SHRINKFILE ([' + @logfile + '], 100)'
        EXEC (@sql)
    END
    ELSE
    BEGIN
        PRINT '  × Nepodarilo sa ziskat logovy subor pre databazu: ' + @dbname
    END

    FETCH NEXT FROM db_cursor INTO @dbname
END

CLOSE db_cursor
DEALLOCATE db_cursor

Odporúčania

• Produkčné databázy ponechaj v FULL režime, ale zabezpeč pravidelné zálohovanie logov.
• Vývojové, testovacie a reportingové databázy nastav na SIMPLE režim.
• Automatizuj zálohovanie logov cez SQL Agent alebo PowerShell (napr. každú hodinu).
• Logy shrinkuj výnimočne, iba ak narástli abnormálne – pravidelné shrinkovanie znižuje výkon.

Nastavenie maximálnej veľkosti transakčného logu je možné jednoducho vykonať cez SSMS GUI, kde pre každú databázu zvlášť nastavíš, koľko GB môže log zaberať. Odporúča sa nastaviť realistickú hranicu podľa charakteru databázy – pre menšie databázy napríklad 1–2 GB, pre väčšie produkčné databázy podľa odhadovaného objemu transakcií. Dôležité je zároveň zabezpečiť pravidelné zálohovanie logov, inak môže log rýchlo naraziť na svoj limit a spôsobiť výpadok.