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.

Az adatbázis-adminisztrátorok (DBA-k) és a Microsoft SQL Servert kezelő DevOps mérnökök számára kevés riasztás okoz akkora azonnali szorongást, mint a 9002-es hiba: A(z) „X” adatbázis tranzakciónaplója megtelt. Amikor a tranzakciónapló megtelik és nem tud tovább nőni, az adatbázis gyakorlatilag írásvédetté válik. Minden INSERT, UPDATE és DELETE művelet leáll, az alkalmazások tranzakciói meghiúsulnak, és a termelés teljesen megáll.

Az SQL Server tranzakciónapló-architektúrájának megértése, a kiváltó ok pontos diagnosztizálása és a gyors helyreállítási eljárások végrehajtása kritikus készségek a magas rendelkezésre állás fenntartásához. Ez az átfogó útmutató feltárja a tranzakciónapló működését, a megtelt napló vészhelyzeti megoldását, valamint azokat az építészeti bevált gyakorlatokat, amelyekkel megelőzhető a probléma megismétlődése.

Az SQL Server tranzakciónapló-architektúrájának megértése

A megtelt tranzakciónapló hatékony hibaelhárításához először meg kell értenie, hogyan írja és kezeli az adatokat az SQL Server.

Write-Ahead Logging (WAL) – Előíró naplózás

Az SQL Server Write-Ahead Logging (WAL) protokollt használ. Amikor adatmódosítás történik, a változást először a memóriában lévő tranzakciónaplóba írja, majd kiírja a lemezen lévő fizikai naplófájlba, mielőtt a tényleges adatlapok frissülnének az adatbázisfájlokban (MDF/NDF). Ez garantálja az ACID (atomicitás, konzisztencia, izoláció, tartósság) megfelelőséget, biztosítva, hogy összeomlás esetén az SQL Server képes legyen a tranzakciók újrajátszására (roll forward) vagy visszavonására (roll back).

Virtuális naplófájlok (VLF-ek) és körkörös naplózás

Belsőleg a fizikai tranzakciónapló-fájl (LDF) kisebb, logikai szegmensekre van osztva, amelyeket virtuális naplófájloknak (VLF) nevezünk. A tranzakciónapló körkörösen működik. Ahogy a naplórekordok íródnak, kitöltenek egy VLF-et, majd továbblépnek a következőre.

Amikor a napló eléri a fizikai fájl végét, megpróbál visszaugrani az elejére. Azonban csak akkor írhat felül egy VLF-et, ha az inaktívként van megjelölve. Ha az összes VLF aktív (ami azt jelenti, hogy olyan naplórekordokat tartalmaznak, amelyekre az SQL Servernek még szüksége van), a napló nem tud körbefordulni. Ha az automatikus növekedés (auto-growth) engedélyezve van és van szabad lemezterület, a fizikai fájl megnő. Ha a lemez megtelt vagy az automatikus növekedés korlátozott, a 9002-es hibát kapja.

Napló csonkítása (Truncation) vs. Napló zsugorítása (Shrinking)

Gyakori tévhit, hogy a napló csonkítása csökkenti a fizikai fájlméretet.
* Napló csonkítása: Az a folyamat, amely során az aktív VLF-eket inaktívként jelölik meg, így a terület újra felhasználhatóvá válik. Ez nem csökkenti az LDF fájl méretét a lemezen.
* Napló zsugorítása: Az a folyamat, amely során fizikailag csökkentik az LDF fájl méretét, és a területet visszaadják az operációs rendszernek.

A Full (teljes) helyreállítási modellben a napló csonkítása csak akkor történik meg, ha a tranzakciónapló-mentés sikeresen befejeződött (feltéve, hogy más folyamatok nem tartják aktívan a naplót).

A „Tranzakciónapló megtelt” hiba (9002-es hiba) diagnosztizálása

Amikor a napló megtelt, az első lépés ne az legyen, hogy vaktában lemezterületet ad hozzá vagy zsugorítja a fájlokat. Meg kell határoznia, miért nem tud a napló csonkulni. Az SQL Server beépített mechanizmust biztosít annak kiderítésére, mi akadályozza a napló újrahasznosítását a sys.databases katalógusnézeten keresztül.

Futtassa a következő T-SQL parancsot a szűk keresztmetszet azonosításához:

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

A tranzakciónaplók aktuális helykihasználtságát a következő paranccsal is ellenőrizheti:

DBCC SQLPERF(LOGSPACE);

Gyakori log_reuse_wait_desc állapotok

  1. LOG_BACKUP: Az adatbázis Full vagy Bulk-Logged helyreállítási modellben van, és mostanában nem készült tranzakciónapló-mentés. Ez a leggyakoribb ok.
  2. ACTIVE_TRANSACTION: Egy hosszan futó tranzakció (pl. egy hatalmas index-újraépítés vagy egy elfelejtett, lezáratlan tranzakció) aktívan tartja a naplót.
  3. REPLICATION / CDC: A tranzakciós replikáció vagy a Change Data Capture (CDC) engedélyezve van, és a Log Reader Agent még nem dolgozta fel a tranzakciókat.
  4. AVAILABILITY_REPLICA: AlwaysOn Availability Group esetén egy másodlagos replika le van választva vagy túl lassan szinkronizál, ami arra kényszeríti az elsődleges replikát, hogy megtartsa a naplórekordokat, amíg azok nem rögzülnek a másodlagoson.

Gyors helyreállítási stratégiák: A probléma megoldása éles környezetben

A visszaadott log_reuse_wait_desc értéktől függően a vészhelyzeti válasz változó lesz. Íme a gyors helyreállítási stratégiák a leggyakoribb forgatókönyvekhez.

1. forgatókönyv: Hiányzó vagy sikertelen naplómentések (LOG_BACKUP)

Ha a várakozási típus LOG_BACKUP, a megoldás egyszerű: el kell készítenie a tranzakciónapló-mentést.

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

Amint a mentés befejeződik, az inaktív VLF-ek csonkulnak, és az SQL Server folytatja a normál működést. Ha a mentési meghajtó megtelt, szükség lehet ideiglenes hálózati megosztásra vagy null eszközre menteni (ez utóbbi erősen ellenjavallt, kivéve, ha az adatbázis könnyen reprodukálható, mivel megszakítja a naplóláncot):

-- FIGYELEM: Ez megszakítja a naplóláncot és veszélyezteti az időpontra történő helyreállítást.
-- Csak akkor használja, ha feltétlenül szükséges, és azonnal kövesse egy FULL mentéssel.
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';

2. forgatókönyv: Hosszan futó aktív tranzakciók (ACTIVE_TRANSACTION)

Ha egyetlen tranzakció órák óta fut, az megakadályozza a napló csonkítását a teljes időtartam alatt. Először azonosítsa a problémás tranzakciót:

DBCC OPENTRAN('YourDatabaseName');

Ez a parancs visszaadja a legrégebbi aktív tranzakciót és annak folyamatazonosítóját (SPID). További részleteket tudhat meg arról, mit csinál az SPID a dinamikus kezelési nézetek (DMV) lekérdezésével:

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>;

Ha a tranzakció egy elszabadult lekérdezés vagy egy elakadt folyamat, előfordulhat, hogy meg kell szakítania a napló felszabadításához.

KILL <SPID>;

Megjegyzés: Egy hatalmas tranzakció megszakítása visszagörgetést (rollback) vált ki, ami jelentős időt vehet igénybe, és átmenetileg további naplótevékenységet generál. Ne indítsa újra az SQL Server szolgáltatást visszagörgetés közben, különben az adatbázis helyreállítási módba kerül az újraindításkor.

3. forgatókönyv: Vészhelyzeti területfoglalás (a lemez 100%-ban megtelt)

Ha az LDF fájl elfoglalta a teljes meghajtót, még mentést sem tud futtatni, mert az SQL Servernek szüksége van egy kis mennyiségű naplóterületre magának a mentési eseménynek a rögzítéséhez. Ebben a forgatókönyvben egy másodlagos naplófájlt kell hozzáadnia egy másik, szabad területtel rendelkező meghajtón.

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

Ez azonnal levegőhöz juttatja az SQL Servert. Amint az adatbázis online állapotba kerül, készítsen egy tranzakciónapló-mentést, ürítse ki a másodlagos naplófájlt, majd távolítsa el:

-- 1. Készítsen naplómentést a napló csonkításához
BACKUP LOG [YourDatabaseName] TO DISK = '...';

-- 2. Ürítse ki az ideiglenes naplófájlt
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);

-- 3. Távolítsa el az ideiglenes naplófájlt
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];

Bevált gyakorlatok a tranzakciónapló megelőzésére és kezelésére

A reaktív hibaelhárítás stresszes és hatással van az SLA-kra. A proaktív építészeti és operatív bevált gyakorlatok bevezetése elengedhetetlen a vállalati adatbázisok stabilitásához.

1. Robusztus, automatizált mentési stratégia megvalósítása

Ha egy adatbázis Full helyreállítási modellben van, a gyakori tranzakciónapló-mentések kötelezőek. A helyreállítási pont célkitűzésétől (RPO) és a tranzakciók mennyiségétől függően a naplómentéseknek 5-15 percenként meg kell történniük.

A vállalati mentési megoldások, mint például a CloudSave, jelentősen leegyszerűsítik ezt a folyamatot. A VDI-n (Virtual Device Interface) keresztül az SQL Serverrel közvetlenül integrálódó CloudSave lehetővé teszi a DBA-k számára, hogy házirend-alapú, nagy gyakoriságú tranzakciónapló-mentéseket konfiguráljanak. Ez biztosítja, hogy a naplók folyamatosan csonkuljanak, biztonságosan titkosítva legyenek, és távoli vagy megváltoztathatatlan felhőtárhelyen legyenek tárolva, megelőzve a LOG_BACKUP várakozási állapotot anélkül, hogy bonyolult egyedi SQL Agent feladatokra lenne szükség.

2. A tranzakciónapló méretezése és a VLF-ek kezelése

Az automatikus növekedésre (auto-growth) hagyatkozni a tranzakciónapló méretének kezelésében veszélyes gyakorlat. Az automatikus növekedési műveletek költségesek és szüneteltetik a tranzakciók feldolgozását, amíg a lemez nullázása megtörténik (kivéve, ha az Instant File Initialization engedélyezve van, ami azonban nem vonatkozik a naplófájlokra).

Ezenkívül a gyakori, kis mértékű automatikus növekedések (pl. 10%-os vagy 50 MB-os lépésekben) VLF-fragmentációhoz vezetnek. A több ezer apró VLF-fel rendelkező tranzakciónapló súlyosan rontja az adatbázis indítási idejét, a mentési teljesítményt és a replikációs késleltetést.

  • Előre méretezze a naplót: Elemezze a legnagyobb karbantartási műveleteit (például index-újraépítések), és méretezze előre az LDF fájlt, hogy növekedés nélkül is befogadja azokat.
  • Állítson be fix automatikus növekedést: Módosítsa az automatikus növekedést százalékról fix méretre (pl. 1 GB vagy 5 GB), hogy biztosítsa a VLF-ek egészséges méretű létrehozását.

A VLF-ek számát a következő lekérdezéssel ellenőrizheti (SQL Server 2017+ esetén):

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

Ha a VLF-ek száma 500 felett van, fontolja meg egy csendes időszak kivárását, a napló minimális méretre zsugorítását, majd kézi, nagy darabokban történő visszaállítását a szükséges méretre.

3. Indexkarbantartási műveletek optimalizálása

Az index-újraépítések teljesen naplózott műveletek, még Bulk-Logged helyreállítási modellben is (az index típusától függően). Egy 500 GB-os index újraépítése legalább 500 GB tranzakciónapló-rekordot generál.

A karbantartás közbeni naplófelfúvódás mérséklése érdekében:
* Használja a SORT_IN_TEMPDB = ON opciót az indexek újraépítésekor. Ez a rendezési fázist a TempDB-be helyezi át, csökkentve a felhasználói adatbázis tranzakciónaplójának terhelését.
* Lehetőség szerint váltson index-újraépítésről index-átszervezésre (reorganize), mivel az átszervezések naplóhatékonyabbak, és megszakíthatók a teljes művelet visszagörgetése nélkül.
* Kötegelje a nagy DELETE vagy UPDATE műveleteket. Ahelyett, hogy 10 millió sort törölne egyetlen tranzakcióban, törölje őket 50 000-es adagokban, véglegesítve (commit) a műveletet, és lehetővé téve a naplómentések számára a napló csonkítását a kötegek között.

4. Magas rendelkezésre állás és replikációs topológiák figyelése

AlwaysOn Availability Groups esetén az elsődleges replika nem csonkíthatja a naplóját, amíg a naplórekordok nem rögzültek az összes szinkron és aszinkron másodlagos replikán.

Ha egy másodlagos replika offline állapotba kerül, vagy ha a hálózati sávszélesség nem tud lépést tartani az elsődleges tranzakció-generálási sebességével, az elsődleges küldési sora megnő, és a napló megtelik (AVAILABILITY_REPLICA várakozási típus).

Valósítson meg robusztus figyelést a SQLServer:Replica > Log Send Queue teljesítményszámlálóra. Ha egy másodlagos replika véglegesen elveszett, el kell távolítania az Availability Groupból, vagy fel kell függesztenie az adatmozgást, hogy lehetővé tegye az elsődleges napló csonkítását.

Következtetés

A megtelt tranzakciónaplóval való találkozás az adatbázis-adminisztrátorok „tűzkeresztsége”, de nem kell, hogy hosszan tartó állásidőhöz vezessen. A Write-Ahead Logging és a VLF-ek működésének megértésével gyorsan diagnosztizálhatja a kiváltó okot a sys.databases segítségével, és alkalmazhatja a megfelelő gyors helyreállítási stratégiát.

A hosszú távú stabilitás a reaktív javításoktól való elmozduláson alapul. A naplófájlok előzetes méretezése, a karbantartási rutinok optimalizálása és az olyan vállalati szintű mentési platformok használata, mint a CloudSave, a szigorú, automatizált naplómentési ütemtervek betartása érdekében biztosítja, hogy a tranzakciónaplók egészségesek, csonkoltak és készen álljanak a nagy áteresztőképességű éles munkaterhelések támogatására.

Kategóriák