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 administratore baza podataka (DBA) i DevOps inženjere koji upravljaju Microsoft SQL Serverom, malo upozorenja izaziva toliko trenutne tjeskobe kao Pogreška 9002: Transakcijski dnevnik za bazu podataka ‘X’ je pun. Kada se transakcijski dnevnik napuni i ne može se povećati, baza podataka efektivno postaje samo za čitanje (read-only). Sve INSERT, UPDATE i DELETE operacije se zaustavljaju, transakcije aplikacije ne uspijevaju, a produkcija staje.

Razumijevanje temeljne arhitekture transakcijskog dnevnika SQL Servera, točna dijagnoza temeljnog uzroka i provođenje brzih postupaka oporavka ključne su vještine za održavanje visoke dostupnosti. Ovaj sveobuhvatni vodič istražuje mehaniku transakcijskog dnevnika, kako riješiti puni dnevnik u hitnim slučajevima i arhitektonske najbolje prakse kako se to ne bi ponovilo.

Razumijevanje arhitekture transakcijskog dnevnika SQL Servera

Da biste učinkovito otklonili poteškoće s punim transakcijskim dnevnikom, prvo morate razumjeti kako SQL Server zapisuje i upravlja podacima.

Write-Ahead Logging (WAL)

SQL Server koristi protokol Write-Ahead Logging (WAL). Kad god dođe do izmjene podataka, promjena se prvo zapisuje u transakcijski dnevnik u memoriji, a zatim ispire u fizičku datoteku dnevnika na disku prije nego što se stvarne stranice podataka ažuriraju u datotekama baze podataka (MDF/NDF). Ovo jamči ACID (atomičnost, dosljednost, izolacija, trajnost) usklađenost, osiguravajući da u slučaju pada, SQL Server može ponoviti (roll forward) ili poništiti (roll back) transakcije.

Virtualne datoteke dnevnika (VLF) i kružno zapisivanje

Interno, fizička datoteka transakcijskog dnevnika (LDF) podijeljena je na manje, logičke segmente koji se nazivaju virtualne datoteke dnevnika (VLF). Transakcijski dnevnik radi kružno. Kako se zapisi dnevnika pišu, oni popunjavaju jedan VLF i prelaze na sljedeći.

Kada dnevnik dosegne kraj fizičke datoteke, pokušava se vratiti na početak. Međutim, može prebrisati VLF samo ako je taj VLF označen kao neaktivan. Ako su svi VLF-ovi aktivni (što znači da sadrže zapise dnevnika koji su još uvijek potrebni SQL Serveru), dnevnik se ne može omotati. Ako je omogućeno automatsko povećanje i prostor na disku je dostupan, fizička datoteka raste. Ako je disk pun ili je automatsko povećanje ograničeno, nailazite na pogrešku 9002.

Skraćivanje dnevnika (Truncation) naspram smanjivanja dnevnika (Shrinking)

Česta je zabluda da skraćivanje dnevnika smanjuje fizičku veličinu datoteke.
* Skraćivanje dnevnika (Log Truncation): Proces označavanja aktivnih VLF-ova kao neaktivnih, čineći prostor dostupnim za ponovnu upotrebu. To ne smanjuje veličinu LDF datoteke na disku.
* Smanjivanje dnevnika (Log Shrinking): Proces fizičkog smanjenja veličine LDF datoteke i vraćanja prostora operativnom sustavu.

U modelu potpunog oporavka (Full Recovery model), skraćivanje dnevnika događa se samo kada je sigurnosna kopija transakcijskog dnevnika uspješno dovršena (pod pretpostavkom da nijedan drugi proces ne drži dnevnik aktivnim).

Dijagnosticiranje pogreške “Transakcijski dnevnik pun” (Pogreška 9002)

Kada je dnevnik pun, vaš prvi korak nije slijepo dodavanje prostora na disku ili smanjivanje datoteka. Morate identificirati zašto se dnevnik ne može skratiti. SQL Server nudi ugrađeni mehanizam koji vam točno govori što sprječava ponovnu upotrebu dnevnika putem sys.databases kataloškog prikaza.

Pokrenite sljedeću T-SQL naredbu kako biste identificirali usko grlo:

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

Također možete provjeriti trenutnu upotrebu prostora vaših transakcijskih dnevnika koristeći:

DBCC SQLPERF(LOGSPACE);

Uobičajena stanja log_reuse_wait_desc

  1. LOG_BACKUP: Baza podataka je u modelu potpunog ili skupno bilježenog oporavka (Full ili Bulk-Logged), a sigurnosna kopija transakcijskog dnevnika nije nedavno napravljena. Ovo je najčešći uzrok.
  2. ACTIVE_TRANSACTION: Transakcija koja dugo traje (npr. masovna obnova indeksa ili zaboravljena nepotvrđena transakcija) drži dnevnik aktivnim.
  3. REPLICATION / CDC: Omogućena je transakcijska replikacija ili Change Data Capture (CDC), a Log Reader Agent još nije obradio transakcije.
  4. AVAILABILITY_REPLICA: U AlwaysOn grupi dostupnosti, sekundarna replika je isključena ili se presporo sinkronizira, prisiljavajući primarnu repliku da zadrži zapise dnevnika dok se ne potvrde na sekundarnoj.

Strategije brzog oporavka: Rješavanje problema u produkciji

Ovisno o vraćenom log_reuse_wait_desc, vaš odgovor na hitne slučajeve će varirati. Evo strategija brzog oporavka za najčešće scenarije.

Scenarij 1: Nedostajuće ili neuspjele sigurnosne kopije dnevnika (LOG_BACKUP)

Ako je vrsta čekanja LOG_BACKUP, rješenje je jednostavno: morate napraviti sigurnosnu kopiju transakcijskog dnevnika.

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

Nakon što se sigurnosna kopija dovrši, neaktivni VLF-ovi će se skratiti i SQL Server će nastaviti s normalnim radom. Ako je vaš pogon za sigurnosne kopije pun, možda ćete morati napraviti sigurnosnu kopiju na privremenu mrežnu dijeljenu mapu ili null uređaj (strogo se ne preporučuje osim ako se baza podataka lako može reproducirati, jer to prekida lanac dnevnika):

-- UPOZORENJE: Ovo prekida lanac dnevnika i ugrožava oporavak do određene točke u vremenu.
-- Koristite samo ako je apsolutno potrebno i odmah nakon toga napravite POTPUNU sigurnosnu kopiju.
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';

Scenarij 2: Dugotrajne aktivne transakcije (ACTIVE_TRANSACTION)

Ako jedna transakcija traje satima, ona sprječava skraćivanje dnevnika tijekom cijelog trajanja. Prvo identificirajte problematičnu transakciju:

DBCC OPENTRAN('YourDatabaseName');

Ova naredba vraća najstariju aktivnu transakciju i njezin ID procesa poslužitelja (SPID). Možete prikupiti više detalja o tome što SPID radi upitom na dinamičke prikaze upravljanja (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>;

Ako je transakcija neispravan upit ili zaustavljeni proces, možda ćete ga morati prekinuti kako biste oslobodili dnevnik.

KILL <SPID>;

Napomena: Prekidanje masivne transakcije pokrenut će povratak (rollback), što može potrajati značajno vrijeme i privremeno će generirati dodatnu aktivnost dnevnika. Nemojte ponovno pokretati uslugu SQL Servera tijekom povratka, inače će baza podataka ući u način oporavka nakon ponovnog pokretanja.

Scenarij 3: Hitna dodjela prostora (Disk je 100% pun)

Ako je LDF datoteka zauzela cijeli pogon, ne možete čak ni pokrenuti sigurnosnu kopiju jer SQL Server zahtijeva malu količinu prostora u dnevniku da bi zabilježio sam događaj sigurnosne kopije. U ovom scenariju morate dodati sekundarnu datoteku dnevnika na drugi pogon s dostupnim prostorom.

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

Ovo odmah daje SQL Serveru prostora za rad. Kada baza podataka bude na mreži, napravite sigurnosnu kopiju transakcijskog dnevnika, ispraznite sekundarnu datoteku dnevnika i uklonite je:

-- 1. Napravite sigurnosnu kopiju dnevnika za skraćivanje
BACKUP LOG [YourDatabaseName] TO DISK = '...';

-- 2. Ispraznite privremenu datoteku dnevnika
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);

-- 3. Uklonite privremenu datoteku dnevnika
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];

Najbolje prakse za prevenciju i upravljanje transakcijskim dnevnikom

Reaktivno otklanjanje poteškoća je stresno i utječe na SLA. Implementacija proaktivnih arhitektonskih i operativnih najboljih praksi ključna je za stabilnost baze podataka u poduzeću.

1. Implementirajte robusnu, automatiziranu strategiju sigurnosnog kopiranja

Ako je baza podataka u modelu potpunog oporavka, česte sigurnosne kopije transakcijskog dnevnika su obavezne. Ovisno o vašem cilju točke oporavka (RPO) i volumenu transakcija, sigurnosne kopije dnevnika trebale bi se odvijati svakih 5 do 15 minuta.

Enterprise rješenja za sigurnosno kopiranje kao što je CloudSave značajno pojednostavljuju ovaj proces. Izravnom integracijom sa SQL Serverom putem VDI-a (Virtual Device Interface), CloudSave omogućuje administratorima baza podataka konfiguriranje sigurnosnih kopija transakcijskog dnevnika visoke frekvencije vođenih politikama. To osigurava da se dnevnici kontinuirano skraćuju, sigurno šifriraju i pohranjuju izvan lokacije ili u nepromjenjivu pohranu u oblaku, sprječavajući stanje čekanja LOG_BACKUP bez potrebe za složenim prilagođenim SQL Agent poslovima.

2. Pravilno dimenzionirajte transakcijski dnevnik i upravljajte VLF-ovima

Oslanjanje na automatsko povećanje za upravljanje veličinom transakcijskog dnevnika opasan je anti-uzorak. Operacije automatskog povećanja su skupe i pauziraju obradu transakcija dok se disk ne inicijalizira nulama (osim ako je omogućena trenutna inicijalizacija datoteka, što se ne odnosi na datoteke dnevnika).

Nadalje, česta, mala automatska povećanja (npr. povećanje za 10% ili 50MB odjednom) dovode do fragmentacije VLF-a. Transakcijski dnevnik s tisućama sićušnih VLF-ova ozbiljno će degradirati vrijeme pokretanja baze podataka, performanse sigurnosnog kopiranja i latenciju replikacije.

  • Prethodno dimenzionirajte dnevnik: Analizirajte svoje najveće operacije održavanja (poput obnove indeksa) i prethodno dimenzionirajte LDF datoteku kako bi ih primila bez rasta.
  • Postavite fiksno automatsko povećanje: Promijenite automatsko povećanje iz postotka u fiksnu veličinu (npr. 1GB ili 5GB) kako biste osigurali da se VLF-ovi stvaraju u zdravoj veličini.

Možete provjeriti svoj broj VLF-ova pomoću sljedećeg upita (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'));

Ako je vaš broj VLF-ova veći od 500, razmislite o čekanju na mirno razdoblje, smanjivanju dnevnika na minimalnu veličinu i ručnom povećanju natrag na potrebnu veličinu u velikim komadima.

3. Optimizirajte operacije održavanja indeksa

Obnove indeksa su operacije koje se u potpunosti bilježe, čak i u modelu skupno bilježenog oporavka (ovisno o vrsti indeksa). Obnova indeksa od 500GB generirat će najmanje 500GB zapisa transakcijskog dnevnika.

Za ublažavanje pretrpanosti dnevnika tijekom održavanja:
* Koristite SORT_IN_TEMPDB = ON prilikom obnove indeksa. Ovo prebacuje fazu sortiranja u TempDB, smanjujući opterećenje na transakcijski dnevnik korisničke baze podataka.
* Prebacite se s obnove indeksa na reorganizaciju indeksa gdje je to moguće, jer su reorganizacije učinkovitije u pogledu dnevnika i mogu se prekinuti bez povratka cijele operacije.
* Grupno izvršavajte velike DELETE ili UPDATE operacije. Umjesto brisanja 10 milijuna redaka u jednoj transakciji, brišite ih u komadima od 50.000, potvrđujući (commit) i dopuštajući sigurnosnim kopijama dnevnika da skrate dnevnik između serija.

4. Nadzirite visoku dostupnost i topologije replikacije

U AlwaysOn grupama dostupnosti, primarna replika ne može skratiti svoj dnevnik dok se zapisi dnevnika ne potvrde na svim sinkronim i asinkronim sekundarnim replikama.

Ako sekundarna replika ode izvan mreže, ili ako mrežna propusnost ne može pratiti brzinu generiranja transakcija primarne replike, red čekanja za slanje primarne replike će rasti, a dnevnik će se napuniti (vrsta čekanja AVAILABILITY_REPLICA).

Implementirajte robusno praćenje za brojač performansi SQLServer:Replica > Log Send Queue. Ako je sekundarna replika trajno izgubljena, morate je ukloniti iz grupe dostupnosti ili obustaviti premještanje podataka kako biste omogućili skraćivanje primarnog dnevnika.

Zaključak

Susret s punim transakcijskim dnevnikom je obred prolaska za administratore baza podataka, ali ne mora rezultirati dugotrajnim prekidom rada. Razumijevanjem mehanike Write-Ahead Logginga i VLF-ova, možete brzo dijagnosticirati temeljni uzrok koristeći sys.databases i primijeniti ispravnu strategiju brzog oporavka.

Dugoročna stabilnost oslanja se na odmak od reaktivnih popravaka. Prethodno dimenzioniranje datoteka dnevnika, optimizacija rutina održavanja i korištenje platformi za sigurnosno kopiranje na razini poduzeća kao što je CloudSave za provođenje strogih, automatiziranih rasporeda sigurnosnog kopiranja dnevnika osigurat će da vaši transakcijski dnevnici ostanu zdravi, skraćeni i spremni za podršku produkcijskim radnim opterećenjima visokog propusnog kapaciteta.

Kategorije