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.

Za skrbnike baz podatkov (DBA) in DevOps inženirje, ki upravljajo Microsoft SQL Server, le malo opozoril povzroči takojšnjo tesnobo kot Napaka 9002: Transakcijski dnevnik za bazo podatkov ‘X’ je poln. Ko se transakcijski dnevnik napolni in se ne more povečati, baza podatkov dejansko postane samo za branje. Vse operacije INSERT, UPDATE in DELETE se ustavijo, transakcije aplikacij spodletijo in produkcija se popolnoma ustavi.

Razumevanje osnovne arhitekture transakcijskega dnevnika SQL Serverja, natančno diagnosticiranje temeljnega vzroka in izvajanje hitrih postopkov za obnovitev so ključne veščine za ohranjanje visoke razpoložljivosti. Ta celovit vodnik raziskuje mehaniko transakcijskega dnevnika, kako rešiti poln dnevnik v nujnih primerih in arhitekturne najboljše prakse za preprečevanje ponovnega pojava te težave.

Razumevanje arhitekture transakcijskega dnevnika SQL Server

Za učinkovito odpravljanje težav s polnim transakcijskim dnevnikom morate najprej razumeti, kako SQL Server zapisuje in upravlja podatke.

Zapisovanje s predhodnim beleženjem (Write-Ahead Logging – WAL)

SQL Server uporablja protokol Write-Ahead Logging (WAL). Kadar koli pride do spremembe podatkov, se sprememba najprej zapiše v transakcijski dnevnik v pomnilniku, nato pa se izprazni v fizično datoteko dnevnika na disku, preden se dejanske podatkovne strani posodobijo v datotekah baze podatkov (MDF/NDF). To zagotavlja skladnost ACID (atomarnost, konsistentnost, izolacija, trajnost), kar zagotavlja, da lahko SQL Server v primeru sesutja ponovi (roll forward) ali razveljavi (roll back) transakcije.

Virtualne datoteke dnevnika (VLF) in krožno beleženje

Interno je fizična datoteka transakcijskega dnevnika (LDF) razdeljena na manjše, logične segmente, imenovane virtualne datoteke dnevnika (VLF). Transakcijski dnevnik deluje krožno. Ko se zapisi dnevnika zapišejo, zapolnijo en VLF in se premaknejo na naslednjega.

Ko dnevnik doseže konec fizične datoteke, poskuša nadaljevati na začetku. Vendar lahko prepiše VLF le, če je ta označen kot neaktiven. Če so vsi VLF-ji aktivni (kar pomeni, da vsebujejo zapise dnevnika, ki jih SQL Server še vedno potrebuje), dnevnik ne more nadaljevati kroženja. Če je omogočena samodejna rast in je prostor na disku na voljo, se fizična datoteka poveča. Če je disk poln ali je samodejna rast omejena, naletite na Napako 9002.

Obrezovanje dnevnika (Truncation) proti krčenju dnevnika (Shrinking)

Pogosta napačna predstava je, da obrezovanje dnevnika zmanjša velikost fizične datoteke.
* Obrezovanje dnevnika (Log Truncation): Postopek označevanja aktivnih VLF-jev kot neaktivnih, s čimer se prostor sprosti za ponovno uporabo. To ne zmanjša velikosti datoteke LDF na disku.
* Krčenje dnevnika (Log Shrinking): Postopek fizičnega zmanjšanja velikosti datoteke LDF in vračanja prostora operacijskemu sistemu.

V modelu popolne obnovitve (Full Recovery) se obrezovanje dnevnika zgodi samo takrat, ko je varnostno kopiranje transakcijskega dnevnika uspešno zaključeno (ob predpostavki, da noben drug proces ne zadržuje dnevnika kot aktivnega).

Diagnosticiranje napake “Transakcijski dnevnik je poln” (Napaka 9002)

Ko je dnevnik poln, vaš prvi korak ne sme biti slepo dodajanje prostora na disku ali krčenje datotek. Ugotoviti morate, zakaj se dnevnik ne more obrezati. SQL Server ponuja vgrajen mehanizem, ki vam prek pogleda kataloga sys.databases natančno pove, kaj preprečuje ponovno uporabo dnevnika.

Za identifikacijo ozkega grla zaženite naslednji ukaz T-SQL:

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

Trenutno zasedenost prostora vaših transakcijskih dnevnikov lahko preverite tudi z:

DBCC SQLPERF(LOGSPACE);

Pogosta stanja log_reuse_wait_desc

  1. LOG_BACKUP: Baza podatkov je v modelu obnovitve Full ali Bulk-Logged in varnostna kopija transakcijskega dnevnika ni bila narejena nedavno. To je najpogostejši vzrok.
  2. ACTIVE_TRANSACTION: Dolgotrajna transakcija (npr. obsežna ponovna izgradnja indeksa ali pozabljena nepotrjena transakcija) ohranja dnevnik aktiven.
  3. REPLICATION / CDC: Omogočena je transakcijska replikacija ali zajemanje sprememb podatkov (CDC), agent za branje dnevnika (Log Reader Agent) pa še ni obdelal transakcij.
  4. AVAILABILITY_REPLICA: V skupini AlwaysOn Availability Group je sekundarna replika prekinjena ali se sinhronizira prepočasi, kar sili primarno repliko, da zadrži zapise dnevnika, dokler niso potrjeni na sekundarni repliki.

Strategije hitre obnovitve: Reševanje težave v produkciji

Glede na vrnjeno vrednost log_reuse_wait_desc se bo vaš nujni odziv razlikoval. Tukaj so strategije hitre obnovitve za najpogostejše scenarije.

Scenarij 1: Manjkajoče ali neuspele varnostne kopije dnevnika (LOG_BACKUP)

Če je tip čakanja LOG_BACKUP, je rešitev preprosta: narediti morate varnostno kopijo transakcijskega dnevnika.

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

Ko se varnostno kopiranje zaključi, bodo neaktivni VLF-ji obrezani in SQL Server bo nadaljeval z običajnim delovanjem. Če je vaš pogon za varnostne kopije poln, boste morda morali varnostno kopijo shraniti na začasno omrežno skupno rabo ali napravo null (zelo odsvetovano, razen če je bazo podatkov enostavno ponovno ustvariti, saj to prekine verigo dnevnika):

-- OPOZORILO: To prekine verigo dnevnika in ogrozi obnovitev na določen čas (point-in-time recovery).
-- Uporabite le, če je nujno potrebno, in takoj zatem naredite POLNO varnostno kopijo.
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';

Scenarij 2: Dolgotrajne aktivne transakcije (ACTIVE_TRANSACTION)

Če ena sama transakcija teče več ur, preprečuje obrezovanje dnevnika za celotno trajanje. Najprej identificirajte problematično transakcijo:

DBCC OPENTRAN('YourDatabaseName');

Ta ukaz vrne najstarejšo aktivno transakcijo in njen ID procesa strežnika (SPID). Več podrobnosti o tem, kaj počne ta SPID, lahko pridobite s poizvedovanjem po dinamičnih upravljalskih pogledih (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>;

Če je transakcija nezaželena poizvedba ali zaustavljen proces, boste morda morali prekiniti proces, da sprostite dnevnik.

KILL <SPID>;

Opomba: Prekinitev obsežne transakcije bo sprožila razveljavitev (rollback), kar lahko traja precej časa in bo začasno ustvarilo dodatno aktivnost v dnevniku. Med razveljavitvijo ne ponovno zaženite storitve SQL Server, sicer bo baza podatkov ob ponovnem zagonu prešla v način obnovitve.

Scenarij 3: Nujna dodelitev prostora (Disk je 100 % poln)

Če je datoteka LDF porabila celoten pogon, ne morete niti zagnati varnostne kopije, ker SQL Server potrebuje majhno količino prostora v dnevniku za zapis samega dogodka varnostnega kopiranja. V tem scenariju morate dodati sekundarno datoteko dnevnika na drug pogon, ki ima na voljo prostor.

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

To SQL Serverju takoj zagotovi prostor za delovanje. Ko je baza podatkov na spletu, naredite varnostno kopijo transakcijskega dnevnika, izpraznite sekundarno datoteko dnevnika in jo odstranite:

-- 1. Naredite varnostno kopijo dnevnika za obrezovanje
BACKUP LOG [YourDatabaseName] TO DISK = '...';

-- 2. Izpraznite začasno datoteko dnevnika
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);

-- 3. Odstranite začasno datoteko dnevnika
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];

Najboljše prakse za preprečevanje in upravljanje transakcijskega dnevnika

Reaktivno odpravljanje težav je stresno in vpliva na SLA. Uvajanje proaktivnih arhitekturnih in operativnih najboljših praks je bistveno za stabilnost baze podatkov v podjetju.

1. Implementirajte robustno, avtomatizirano strategijo varnostnega kopiranja

Če je baza podatkov v modelu obnovitve Full, so pogoste varnostne kopije transakcijskega dnevnika obvezne. Glede na vaš cilj točke obnovitve (RPO) in obseg transakcij, bi morale varnostne kopije dnevnika potekati vsakih 5 do 15 minut.

Rešitve za varnostno kopiranje v podjetjih, kot je CloudSave, znatno poenostavijo ta proces. Z neposredno integracijo s SQL Serverjem prek VDI (Virtual Device Interface) CloudSave omogoča skrbnikom baz podatkov konfiguracijo varnostnih kopij transakcijskega dnevnika z visoko frekvenco, ki temeljijo na politikah. To zagotavlja, da so dnevniki nenehno obrezani, varno šifrirani in shranjeni zunaj lokacije ali v nespremenljivem shrambišču v oblaku, kar preprečuje stanje čakanja LOG_BACKUP brez potrebe po zapletenih opravilih SQL Agent po meri.

2. Pravilno dimenzionirajte transakcijski dnevnik in upravljajte VLF-je

Zanašanje na samodejno rast za upravljanje velikosti transakcijskega dnevnika je nevaren vzorec. Operacije samodejne rasti so drage in začasno ustavijo obdelavo transakcij, medtem ko se disk inicializira z ničlami (razen če je omogočena hitra inicializacija datotek, kar pa ne velja za datoteke dnevnika).

Poleg tega pogoste, majhne samodejne rasti (npr. povečanje za 10 % ali 50 MB naenkrat) vodijo do fragmentacije VLF. Transakcijski dnevnik s tisoči majhnih VLF-jev bo močno poslabšal čase zagona baze podatkov, zmogljivost varnostnega kopiranja in zakasnitev replikacije.

  • Vnaprej določite velikost dnevnika: Analizirajte svoje največje vzdrževalne operacije (kot so ponovne izgradnje indeksov) in vnaprej določite velikost datoteke LDF, da jih bo lahko sprejela brez rasti.
  • Nastavite fiksno samodejno rast: Spremenite samodejno rast iz odstotka v fiksno velikost (npr. 1 GB ali 5 GB), da zagotovite, da so VLF-ji ustvarjeni v zdravi velikosti.

Število svojih VLF-jev lahko preverite z naslednjo poizvedbo (za 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'));

Če je vaše število VLF-jev nad 500, razmislite o tem, da počakate na mirno obdobje, skrčite dnevnik na minimalno velikost in ga ročno povečate nazaj na zahtevano velikost v velikih kosih.

3. Optimizirajte operacije vzdrževanja indeksov

Ponovne izgradnje indeksov so v celoti zabeležene operacije, tudi v modelu obnovitve Bulk-Logged (odvisno od vrste indeksa). Ponovna izgradnja 500 GB indeksa bo ustvarila vsaj 500 GB zapisov transakcijskega dnevnika.

Za ublažitev povečanja dnevnika med vzdrževanjem:
* Pri ponovni izgradnji indeksov uporabite SORT_IN_TEMPDB = ON. To razbremeni fazo razvrščanja v TempDB, kar zmanjša obremenitev transakcijskega dnevnika uporabniške baze podatkov.
* Kjer je mogoče, preklopite s ponovne izgradnje indeksov na reorganizacijo indeksov, saj so reorganizacije bolj učinkovite pri beleženju in jih je mogoče prekiniti, ne da bi razveljavili celotno operacijo.
* Pakirajte velike operacije DELETE ali UPDATE. Namesto brisanja 10 milijonov vrstic v eni transakciji, jih izbrišite v kosih po 50.000, pri čemer potrdite in dovolite varnostnim kopijam dnevnika, da obrežejo dnevnik med paketi.

4. Spremljajte topologije visoke razpoložljivosti in replikacije

V skupinah AlwaysOn Availability Groups primarna replika ne more obrezati svojega dnevnika, dokler zapisi dnevnika niso potrjeni na vseh sinhronih in asinhronih sekundarnih replikah.

Če sekundarna replika preide v stanje brez povezave ali če omrežna pasovna širina ne more slediti hitrosti ustvarjanja transakcij primarne replike, bo čakalna vrsta za pošiljanje primarne replike zrasla in dnevnik se bo napolnil (tip čakanja AVAILABILITY_REPLICA).

Implementirajte robustno spremljanje za števec zmogljivosti SQLServer:Replica > Log Send Queue. Če je sekundarna replika trajno izgubljena, jo morate odstraniti iz skupine Availability Group ali začasno ustaviti premikanje podatkov, da omogočite obrezovanje primarnega dnevnika.

Zaključek

Nalet na poln transakcijski dnevnik je “obred prehoda” za skrbnike baz podatkov, vendar to ne pomeni nujno dolgotrajnega izpada. Z razumevanjem mehanike Write-Ahead Logging in VLF-jev lahko hitro diagnosticirate temeljni vzrok s pomočjo sys.databases in uporabite pravilno strategijo hitre obnovitve.

Dolgoročna stabilnost temelji na opuščanju reaktivnih popravkov. Vnaprejšnje določanje velikosti datotek dnevnika, optimizacija vzdrževalnih rutin in uporaba platform za varnostno kopiranje na ravni podjetja, kot je CloudSave, za uveljavljanje strogih, avtomatiziranih urnikov varnostnega kopiranja dnevnika, bodo zagotovili, da bodo vaši transakcijski dnevniki ostali zdravi, obrezani in pripravljeni na podporo produkcijskim delovnim obremenitvam z visoko prepustnostjo.

Kategorije