Categories
Database Backup

** Discover expert strategies for preventing and resolving MSSQL transaction log full errors (Error 9002). Learn rapid recovery techniques, VLF management, and architectural best practices for DBAs.

Pro databázové administrátory (DBA) a DevOps inženýry spravující Microsoft SQL Server existuje jen málo upozornění, která vyvolávají tak okamžitou úzkost jako Chyba 9002: Transakční protokol pro databázi ‚X‘ je plný. Když se transakční protokol zaplní a nemůže dále růst, databáze se fakticky přepne do režimu jen pro čtení. Všechny operace INSERT, UPDATE a DELETE se zastaví, transakce aplikací selžou a produkce se zcela zastaví.

Pochopení základní architektury transakčního protokolu SQL Serveru, přesná diagnostika hlavní příčiny a provádění rychlých postupů obnovy jsou kritickými dovednostmi pro udržení vysoké dostupnosti. Tento komplexní průvodce zkoumá mechanismy transakčního protokolu, jak vyřešit plný protokol v nouzové situaci a jaké jsou osvědčené architektonické postupy, aby se to již neopakovalo.

Pochopení architektury transakčního protokolu SQL Serveru

Abyste mohli efektivně řešit problémy s plným transakčním protokolem, musíte nejprve pochopit, jak SQL Server zapisuje a spravuje data.

Write-Ahead Logging (WAL)

SQL Server používá protokol Write-Ahead Logging (WAL). Kdykoli dojde k úpravě dat, změna se nejprve zapíše do transakčního protokolu v paměti a poté se zapíše do fyzického souboru protokolu na disku, než jsou skutečné datové stránky aktualizovány v databázových souborech (MDF/NDF). To zaručuje shodu s ACID (atomicitu, konzistenci, izolaci, trvanlivost), což zajišťuje, že v případě havárie může SQL Server transakce znovu přehrát (roll forward) nebo vrátit zpět (roll back).

Virtuální soubory protokolu (VLF) a cyklické protokolování

Interně je fyzický soubor transakčního protokolu (LDF) rozdělen na menší logické segmenty nazývané virtuální soubory protokolu (VLF). Transakční protokol funguje cyklicky. Jak jsou záznamy protokolu zapisovány, zaplňují jeden VLF a přecházejí na další.

Když protokol dosáhne konce fyzického souboru, pokusí se vrátit na začátek. Může však přepsat VLF pouze tehdy, pokud je tento VLF označen jako neaktivní. Pokud jsou všechny VLF aktivní (což znamená, že obsahují záznamy protokolu, které SQL Server stále vyžaduje), protokol se nemůže vrátit na začátek. Pokud je povoleno automatické zvětšování a je k dispozici místo na disku, fyzický soubor se zvětší. Pokud je disk plný nebo je automatické zvětšování omezeno, narazíte na chybu 9002.

Zkrácení protokolu (Truncation) vs. zmenšení protokolu (Shrinking)

Častým omylem je, že zkrácení protokolu zmenší fyzickou velikost souboru.
* Zkrácení protokolu (Log Truncation): Proces označení aktivních VLF jako neaktivních, čímž se prostor uvolní pro opětovné použití. Nezmenšuje velikost souboru LDF na disku.
* Zmenšení protokolu (Log Shrinking): Proces fyzického zmenšení velikosti souboru LDF a vrácení prostoru operačnímu systému.

V modelu úplného obnovení (Full Recovery) dochází ke zkrácení protokolu pouze tehdy, když je úspěšně dokončeno zálohování transakčního protokolu (za předpokladu, že protokol nedrží aktivní žádné jiné procesy).

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

Když je protokol plný, vaším prvním krokem by nemělo být bezhlavé přidávání místa na disku nebo zmenšování souborů. Musíte identifikovat, proč protokol nelze zkrátit. SQL Server poskytuje vestavěný mechanismus, který vám přesně řekne, co brání opětovnému použití protokolu prostřednictvím zobrazení katalogu sys.databases.

Spusťte následující příkaz T-SQL pro identifikaci úzkého hrdla:

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

Aktuální využití prostoru vašich transakčních protokolů můžete také zkontrolovat pomocí:

DBCC SQLPERF(LOGSPACE);

Běžné stavy log_reuse_wait_desc

  1. LOG_BACKUP: Databáze je v modelu obnovení Full nebo Bulk-Logged a v poslední době nebyla provedena záloha transakčního protokolu. Toto je nejčastější příčina.
  2. ACTIVE_TRANSACTION: Dlouhotrvající transakce (např. masivní přestavba indexu nebo zapomenutá nepotvrzená transakce) udržuje protokol aktivní.
  3. REPLICATION / CDC: Je povolena transakční replikace nebo Change Data Capture (CDC) a agent čtečky protokolu (Log Reader Agent) ještě nezpracoval transakce.
  4. AVAILABILITY_REPLICA: Ve skupině dostupnosti AlwaysOn je sekundární replika odpojena nebo se synchronizuje příliš pomalu, což nutí primární repliku uchovávat záznamy protokolu, dokud nejsou potvrzeny na sekundární replice.

Strategie rychlé obnovy: Řešení problému v produkci

V závislosti na vrácené hodnotě log_reuse_wait_desc se bude vaše nouzová reakce lišit. Zde jsou strategie rychlé obnovy pro nejčastější scénáře.

Scénář 1: Chybějící nebo selhávající zálohy protokolu (LOG_BACKUP)

Pokud je typ čekání LOG_BACKUP, řešení je přímočaré: musíte zálohovat transakční protokol.

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

Jakmile se záloha dokončí, neaktivní VLF budou zkráceny a SQL Server obnoví normální provoz. Pokud je váš zálohovací disk plný, možná budete muset zálohovat na dočasnou síťovou sdílenou složku nebo na nulové zařízení (důrazně se nedoporučuje, pokud databázi nelze snadno reprodukovat, protože to přeruší řetězec protokolu):

-- VAROVÁNÍ: Toto přeruší řetězec protokolu a ohrozí obnovu k určitému bodu v čase (point-in-time recovery).
-- Používejte pouze v případě naprosté nutnosti a ihned poté proveďte ÚPLNOU zálohu.
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';

Scénář 2: Dlouhotrvající aktivní transakce (ACTIVE_TRANSACTION)

Pokud jedna transakce běží hodiny, brání zkrácení protokolu po celou dobu trvání. Nejprve identifikujte problematickou transakci:

DBCC OPENTRAN('YourDatabaseName');

Tento příkaz vrátí nejstarší aktivní transakci a její ID procesu serveru (SPID). Další podrobnosti o tom, co SPID dělá, můžete získat dotazem na dynamická zobrazení 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>;

Pokud je transakce chybný dotaz nebo zaseknutý proces, možná jej budete muset ukončit, abyste uvolnili protokol.

KILL <SPID>;

Poznámka: Ukončení masivní transakce spustí vrácení zpět (rollback), což může trvat značnou dobu a dočasně vygeneruje další aktivitu protokolu. Během rollbacku nerestartujte službu SQL Server, jinak databáze po restartu přejde do režimu obnovy.

Scénář 3: Nouzové přidělení prostoru (Disk je 100% plný)

Pokud soubor LDF spotřeboval celý disk, nemůžete ani spustit zálohu, protože SQL Server vyžaduje malé množství místa v protokolu pro zaznamenání samotné události zálohování. V tomto scénáři musíte přidat sekundární soubor protokolu na jiný disk s dostupným místem.

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

To okamžitě poskytne SQL Serveru prostor k dýchání. Jakmile je databáze online, proveďte zálohu transakčního protokolu, vyprázdněte sekundární soubor protokolu a odstraňte jej:

-- 1. Proveďte zálohu protokolu pro zkrácení protokolu
BACKUP LOG [YourDatabaseName] TO DISK = '...';

-- 2. Vyprázdněte dočasný soubor protokolu
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);

-- 3. Odstraňte dočasný soubor protokolu
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];

Osvědčené postupy pro prevenci a správu transakčního protokolu

Reaktivní řešení problémů je stresující a ovlivňuje SLA. Implementace proaktivních architektonických a provozních osvědčených postupů je nezbytná pro stabilitu podnikových databází.

1. Implementujte robustní, automatizovanou strategii zálohování

Pokud je databáze v modelu obnovení Full, jsou časté zálohy transakčního protokolu povinné. V závislosti na vašem cíli bodu obnovy (RPO) a objemu transakcí by zálohy protokolu měly probíhat každých 5 až 15 minut.

Podniková řešení zálohování, jako je CloudSave, tento proces výrazně zjednodušují. Díky přímé integraci s SQL Serverem přes VDI (Virtual Device Interface) umožňuje CloudSave administrátorům konfigurovat zálohy transakčních protokolů řízené zásadami a s vysokou frekvencí. To zajišťuje, že protokoly jsou průběžně zkracovány, bezpečně šifrovány a ukládány mimo pracoviště nebo do neměnného cloudového úložiště, čímž se předchází stavu čekání LOG_BACKUP bez nutnosti složitých vlastních úloh SQL Agenta.

2. Správně dimenzujte transakční protokol a spravujte VLF

Spoléhat se na automatické zvětšování při správě velikosti transakčního protokolu je nebezpečný anti-pattern. Operace automatického zvětšování jsou nákladné a pozastavují zpracování transakcí, zatímco je disk nulován (pokud není povolena inicializace souborů okamžitě, což se nevztahuje na soubory protokolu).

Kromě toho časté, malé automatické zvětšování (např. zvětšení o 10 % nebo 50 MB najednou) vede k fragmentaci VLF. Transakční protokol s tisíci malými VLF výrazně zhorší dobu spuštění databáze, výkon zálohování a latenci replikace.

  • Předem dimenzujte protokol: Analyzujte své největší operace údržby (jako jsou přestavby indexů) a předem dimenzujte soubor LDF tak, aby je pojal bez nutnosti zvětšování.
  • Nastavte pevné automatické zvětšování: Změňte automatické zvětšování z procenta na pevnou velikost (např. 1 GB nebo 5 GB), abyste zajistili, že VLF budou vytvořeny ve zdravé velikosti.

Počet VLF můžete zkontrolovat pomocí následujícího dotazu (pro 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'));

Pokud je váš počet VLF vyšší než 500, zvažte počkání na klidné období, zmenšení protokolu na minimální velikost a ruční zvětšení zpět na požadovanou velikost ve velkých blocích.

3. Optimalizujte operace údržby indexů

Přestavby indexů jsou plně protokolované operace, a to i v modelu obnovení Bulk-Logged (v závislosti na typu indexu). Přestavba 500GB indexu vygeneruje minimálně 500 GB záznamů transakčního protokolu.

Pro zmírnění bobtnání protokolu během údržby:
* Při přestavbě indexů použijte SORT_IN_TEMPDB = ON. Tím se fáze řazení přesune do TempDB, čímž se sníží zátěž transakčního protokolu uživatelské databáze.
* Pokud je to možné, přepněte z přestavby indexů na reorganizaci indexů, protože reorganizace jsou efektivnější z hlediska protokolu a lze je přerušit, aniž by se vracela celá operace.
* Dávkujte velké operace DELETE nebo UPDATE. Místo mazání 10 milionů řádků v jedné transakci je mažte v dávkách po 50 000, přičemž mezi dávkami potvrzujte (commit) a umožněte zálohám protokolu zkrátit protokol.

4. Monitorujte topologie vysoké dostupnosti a replikace

Ve skupinách dostupnosti AlwaysOn nemůže primární replika zkrátit svůj protokol, dokud nebyly záznamy protokolu potvrzeny na všech synchronních a asynchronních sekundárních replikách.

Pokud sekundární replika přejde do režimu offline nebo pokud šířka pásma sítě nestíhá rychlost generování transakcí primární repliky, fronta odesílání primární repliky poroste a protokol se zaplní (typ čekání AVAILABILITY_REPLICA).

Implementujte robustní monitorování čítače výkonu SQLServer:Replica > Log Send Queue. Pokud je sekundární replika trvale ztracena, musíte ji odebrat ze skupiny dostupnosti nebo pozastavit přesun dat, aby se primární protokol mohl zkrátit.

Závěr

Setkání s plným transakčním protokolem je pro databázové administrátory křest ohněm, ale nemusí nutně vést k dlouhým prostojům. Pochopením mechanismů Write-Ahead Logging a VLF můžete rychle diagnostikovat hlavní příčinu pomocí sys.databases a aplikovat správnou strategii rychlé obnovy.

Dlouhodobá stabilita závisí na přechodu od reaktivních oprav. Předběžné dimenzování souborů protokolu, optimalizace rutin údržby a využívání podnikových platforem pro zálohování, jako je CloudSave, k vynucení přísných, automatizovaných plánů zálohování protokolu zajistí, že vaše transakční protokoly zůstanou zdravé, zkrácené a připravené podporovat produkční úlohy s vysokou propustností.

Rubriky