Plný transakčný denník MSSQL: Stratégie prevencie a rýchlej obnovy

Pre správcov databáz (DBA) a DevOps inžinierov spravujúcich Microsoft SQL Server existuje len málo upozornení, ktoré vyvolávajú takú okamžitú úzkosť ako Chyba 9002: Transakčný denník pre databázu ‚X‘ je plný. Keď sa transakčný denník zaplní a nemôže narásť, databáza sa fakticky stane iba na čítanie. Všetky operácie INSERT, UPDATE a DELETE sa zastavia, transakcie aplikácií zlyhajú a produkcia sa úplne zastaví.

Pochopenie základnej architektúry transakčného denníka SQL Servera, presná diagnostika hlavnej príčiny a vykonanie rýchlych postupov obnovy sú kritickými zručnosťami pre udržanie vysokej dostupnosti. Tento komplexný sprievodca skúma mechaniku transakčného denníka, spôsob, ako vyriešiť plný denník v núdzovej situácii, a osvedčené architektonické postupy, ako zabrániť tomu, aby sa to opakovalo.

Pochopenie architektúry transakčného denníka SQL Servera

Aby ste mohli efektívne riešiť problémy s plným transakčným denníkom, musíte najprv pochopiť, ako SQL Server zapisuje a spravuje dáta.

Write-Ahead Logging (WAL)

SQL Server používa protokol Write-Ahead Logging (WAL). Kedykoľvek dôjde k úprave údajov, zmena sa najprv zapíše do transakčného denníka v pamäti a potom sa prenesie do fyzického súboru denníka na disku predtým, než sa aktualizujú skutočné dátové stránky v databázových súboroch (MDF/NDF). To zaručuje súlad s ACID (atomicitu, konzistenciu, izoláciu, trvanlivosť), čím sa zabezpečí, že v prípade pádu systému môže SQL Server zopakovať (roll forward) alebo vrátiť späť (roll back) transakcie.

Virtuálne súbory denníka (VLF) a kruhové zapisovanie

Interne je fyzický súbor transakčného denníka (LDF) rozdelený na menšie logické segmenty nazývané virtuálne súbory denníka (VLF). Transakčný denník funguje kruhovo. Ako sa záznamy denníka zapisujú, zapĺňajú jeden VLF a prechádzajú na ďalší.

Keď denník dosiahne koniec fyzického súboru, pokúsi sa vrátiť na začiatok. VLF však môže prepísať iba vtedy, ak je označený ako neaktívny. Ak sú všetky VLF aktívne (čo znamená, že obsahujú záznamy denníka, ktoré SQL Server stále potrebuje), denník sa nemôže obtočiť. Ak je povolené automatické zväčšovanie a je k dispozícii miesto na disku, fyzický súbor narastie. Ak je disk plný alebo je automatické zväčšovanie obmedzené, narazíte na chybu 9002.

Skrátenie denníka (Truncation) vs. zmenšenie denníka (Shrinking)

Častým omylom je, že skrátenie denníka zmenší fyzickú veľkosť súboru.
* Skrátenie denníka (Log Truncation): Proces označenia aktívnych VLF ako neaktívnych, čím sa uvoľní miesto na opätovné použitie. Nezmenšuje veľkosť súboru LDF na disku.
* Zmenšenie denníka (Log Shrinking): Proces fyzického zmenšenia veľkosti súboru LDF a vrátenia miesta operačnému systému.

V modeli úplného obnovenia (Full Recovery) dochádza k skráteniu denníka iba vtedy, keď je úspešne dokončená záloha transakčného denníka (za predpokladu, že žiadne iné procesy nedržia denník aktívny).

Diagnostika chyby „Transakčný denník je plný“ (Chyba 9002)

Keď je denník plný, vaším prvým krokom nie je bezhlavé pridávanie miesta na disku alebo zmenšovanie súborov. Musíte identifikovať, prečo sa denník nemôže skrátiť. SQL Server poskytuje vstavaný mechanizmus, ktorý vám presne povie, čo bráni opätovnému použitiu denníka prostredníctvom zobrazenia katalógu sys.databases.

Spustite nasledujúci príkaz T-SQL na identifikáciu úzkeho hrdla:

SELECT 
    name AS DatabaseName, 
    recovery_model_desc AS RecoveryModel, 
    log_reuse_wait_desc AS LogReuseWaitReason
FROM sys.databases
WHERE name = 'YourDatabaseName';

Aktuálne využitie miesta vašich transakčných denníkov môžete skontrolovať aj pomocou:

DBCC SQLPERF(LOGSPACE);

Bežné stavy log_reuse_wait_desc

1. LOG_BACKUP: Databáza je v modeli obnovy Full alebo Bulk-Logged a v poslednom čase nebola vykonaná záloha transakčného denníka. Toto je najčastejšia príčina.
2. ACTIVE_TRANSACTION: Dlhotrvajúca transakcia (napr. masívna prestavba indexu alebo zabudnutá nepotvrdená transakcia) udržiava denník aktívny.
3. REPLICATION / CDC: Je povolená transakčná replikácia alebo Change Data Capture (CDC) a agent čítačky denníka (Log Reader Agent) ešte nespracoval transakcie.
4. AVAILABILITY_REPLICA: V skupine dostupnosti AlwaysOn je sekundárna replika odpojená alebo sa synchronizuje príliš pomaly, čo núti primárnu repliku ponechať si záznamy denníka, kým nie sú potvrdené na sekundárnej replike.

Stratégie rýchlej obnovy: Riešenie problému v produkcii

V závislosti od vráteného stavu log_reuse_wait_desc sa bude líšiť vaša reakcia na núdzovú situáciu. Tu sú stratégie rýchlej obnovy pre najbežnejšie scenáre.

Scenár 1: Chýbajúce alebo zlyhávajúce zálohy denníka (LOG_BACKUP)

Ak je typ čakania LOG_BACKUP, riešenie je priamočiare: musíte zálohovať transakčný denník.

BACKUP LOG [YourDatabaseName] 
TO DISK = 'N:BackupsYourDatabaseName_EmergencyLog.trn' 
WITH COMPRESSION, STATS = 10;

Po dokončení zálohovania sa neaktívne VLF skrátia a SQL Server obnoví normálnu prevádzku. Ak je váš disk so zálohami plný, možno budete musieť zálohovať na dočasný sieťový zdieľaný priečinok alebo na nulové zariadenie (dôrazne sa neodporúča, pokiaľ nie je databáza ľahko obnoviteľná, pretože to preruší reťazec denníka):

-- VAROVANIE: Toto preruší reťazec denníka a ohrozí obnovu k určitému bodu v čase (point-in-time recovery).
-- Použite len v nevyhnutnom prípade a ihneď potom vykonajte ÚPLNÚ zálohu.
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';

Scenár 2: Dlhotrvajúce aktívne transakcie (ACTIVE_TRANSACTION)

Ak jedna transakcia beží hodiny, bráni skráteniu denníka po celú dobu trvania. Najprv identifikujte problematickú transakciu:

DBCC OPENTRAN('YourDatabaseName');

Tento príkaz vráti najstaršiu aktívnu transakciu a jej ID procesu servera (SPID). Viac podrobností o tom, čo SPID robí, môžete získať dotazovaním na dynamické zobrazenia správy (DMV):

SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    r.start_time,
    r.status,
    r.command,
    t.text AS QueryText
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.session_id = <SPID_FROM_DBCC_OPENTRAN>;

Ak je transakcia chybný dotaz alebo zaseknutý proces, možno ho budete musieť ukončiť, aby ste uvoľnili denník.

KILL <SPID>;

Poznámka: Ukončenie masívnej transakcie spustí vrátenie zmien (rollback), čo môže trvať značný čas a dočasne vygeneruje ďalšiu aktivitu v denníku. Počas vrátenia zmien nereštartujte službu SQL Server, inak databáza po reštarte prejde do režimu obnovy.

Scenár 3: Núdzové pridelenie miesta (Disk je na 100 % plný)

Ak súbor LDF spotreboval celý disk, nemôžete ani spustiť zálohovanie, pretože SQL Server vyžaduje malé množstvo miesta v denníku na zaznamenanie samotnej udalosti zálohovania. V tomto scenári musíte pridať sekundárny súbor denníka na iný disk s dostupným miestom.

ALTER DATABASE [YourDatabaseName]
ADD LOG FILE 
(
    NAME = N'YourDatabaseName_Log2',
    FILENAME = N'E:TempLogsYourDatabaseName_Log2.ldf',
    SIZE = 5GB,
    MAXSIZE = 50GB,
    FILEGROWTH = 1GB
);

To okamžite poskytne SQL Serveru priestor na dýchanie. Keď je databáza online, vykonajte zálohu transakčného denníka, vyprázdnite sekundárny súbor denníka a odstráňte ho:

-- 1. Vykonajte zálohu denníka na jeho skrátenie
BACKUP LOG [YourDatabaseName] TO DISK = '...';

-- 2. Vyprázdnite dočasný súbor denníka
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);

-- 3. Odstráňte dočasný súbor denníka
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];

Osvedčené postupy pre prevenciu a správu transakčného denníka

Reaktívne riešenie problémov je stresujúce a ovplyvňuje SLA. Implementácia proaktívnych architektonických a prevádzkových osvedčených postupov je nevyhnutná pre stabilitu podnikovej databázy.

1. Implementujte robustnú, automatizovanú stratégiu zálohovania

Ak je databáza v modeli obnovy Full, časté zálohovanie transakčného denníka je povinné. V závislosti od vášho cieľa bodu obnovy (RPO) a objemu transakcií by sa zálohy denníka mali vykonávať každých 5 až 15 minút.

Podnikové riešenia zálohovania, ako je CloudSave, tento proces výrazne zjednodušujú. Vďaka priamej integrácii s SQL Serverom cez VDI (Virtual Device Interface) umožňuje CloudSave správcom databáz konfigurovať vysokofrekvenčné zálohovanie transakčného denníka riadené politikami. To zabezpečuje, že denníky sú nepretržite skracované, bezpečne šifrované a uložené mimo pracoviska alebo v nemennom cloudovom úložisku, čím sa predchádza stavu čakania LOG_BACKUP bez potreby zložitých vlastných úloh SQL Agenta.

2. Správne dimenzujte transakčný denník a spravujte VLF

Spoliehanie sa na automatické zväčšovanie pri správe veľkosti transakčného denníka je nebezpečný anti-pattern. Operácie automatického zväčšovania sú nákladné a pozastavujú spracovanie transakcií, kým sa disk neinicializuje nulami (pokiaľ nie je povolená inicializácia súborov okamžite, čo sa nevzťahuje na súbory denníka).

Okrem toho časté, malé automatické zväčšovania (napr. zväčšenie o 10 % alebo 50 MB naraz) vedú k fragmentácii VLF. Transakčný denník s tisíckami malých VLF výrazne zhorší časy spustenia databázy, výkon zálohovania a latenciu replikácie.

• Prednastavte veľkosť denníka: Analyzujte svoje najväčšie operácie údržby (ako sú prestavby indexov) a vopred nastavte veľkosť súboru LDF tak, aby ich zvládol bez zväčšovania.
• Nastavte pevné automatické zväčšovanie: Zmeňte automatické zväčšovanie z percenta na pevnú veľkosť (napr. 1 GB alebo 5 GB), aby ste zabezpečili, že VLF budú vytvorené vo vhodnej veľkosti.

Počet svojich VLF môžete skontrolovať pomocou nasledujúceho dotazu (pre SQL Server 2017+):

SELECT 
    db_name(database_id) AS DatabaseName,
    COUNT(vlf_sequence_number) AS VLF_Count
FROM sys.dm_db_log_info(DB_ID('YourDatabaseName'));

Ak je váš počet VLF vyšší ako 500, zvážte počkanie na pokojné obdobie, zmenšenie denníka na minimálnu veľkosť a manuálne zväčšenie späť na požadovanú veľkosť vo veľkých blokoch.

3. Optimalizujte operácie údržby indexov

Prestavby indexov sú plne zaznamenávané operácie, dokonca aj v modeli obnovy Bulk-Logged (v závislosti od typu indexu). Prestavba 500 GB indexu vygeneruje aspoň 500 GB záznamov transakčného denníka.

Na zmiernenie nafúknutia denníka počas údržby:
* Pri prestavbe indexov použite SORT_IN_TEMPDB = ON. Tým sa fáza triedenia presunie do TempDB, čím sa zníži záťaž transakčného denníka používateľskej databázy.
* Ak je to možné, prepnite z prestavby indexov na reorganizáciu indexov, pretože reorganizácie sú efektívnejšie z hľadiska denníka a možno ich prerušiť bez vrátenia celej operácie.
* Dávkujte veľké operácie DELETE alebo UPDATE. Namiesto odstránenia 10 miliónov riadkov v jednej transakcii ich odstráňte v dávkach po 50 000, pričom medzi dávkami potvrďte zmeny a umožnite zálohám denníka skrátiť denník.

4. Monitorujte topológie vysokej dostupnosti a replikácie

V skupinách dostupnosti AlwaysOn nemôže primárna replika skrátiť svoj denník, kým záznamy denníka nie sú potvrdené na všetkých synchrónnych a asynchrónnych sekundárnych replikách.

Ak sekundárna replika prejde do režimu offline alebo ak šírka pásma siete nestíha rýchlosť generovania transakcií primárnej repliky, odosielací rad primárnej repliky narastie a denník sa zaplní (typ čakania AVAILABILITY_REPLICA).

Implementujte robustné monitorovanie pre čítač výkonu SQLServer:Replica > Log Send Queue. Ak je sekundárna replika trvalo stratená, musíte ju odstrániť zo skupiny dostupnosti alebo pozastaviť presun údajov, aby sa primárny denník mohol skrátiť.

Záver

Stretnutie s plným transakčným denníkom je krst ohňom pre správcov databáz, ale nemusí viesť k dlhým prestojom. Pochopením mechaniky Write-Ahead Logging a VLF môžete rýchlo diagnostikovať hlavnú príčinu pomocou sys.databases a použiť správnu stratégiu rýchlej obnovy.

Dlhodobá stabilita závisí od prechodu od reaktívnych opráv. Prednastavenie veľkosti súborov denníka, optimalizácia rutín údržby a využívanie podnikových platforiem na zálohovanie, ako je CloudSave, na vynútenie prísnych, automatizovaných plánov zálohovania denníka zabezpečí, že vaše transakčné denníky zostanú zdravé, skrátené a pripravené podporovať produkčné pracovné zaťaženie s vysokou priepustnosťou.