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 anksioznosti kao Greška 9002: Transakcijski log za bazu podataka ‘X’ je pun. Kada se transakcijski log popuni i ne može se proširiti, 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 osnovne arhitekture transakcijskog loga SQL Servera, precizna dijagnostika uzroka i izvršavanje brzih procedura oporavka su kritične vještine za održavanje visoke dostupnosti. Ovaj sveobuhvatni vodič istražuje mehaniku transakcijskog loga, kako riješiti pun log u hitnim slučajevima i arhitektonske najbolje prakse kako bi se spriječilo da se to ponovi.

Razumijevanje arhitekture transakcijskog loga SQL Servera

Da biste efikasno otklonili probleme s punim transakcijskim logom, prvo morate razumjeti kako SQL Server zapisuje i upravlja podacima.

Write-Ahead Logging (WAL)

SQL Server koristi Write-Ahead Logging (WAL) protokol. Kad god dođe do modifikacije podataka, promjena se prvo zapisuje u transakcijski log u memoriji, a zatim se ispire (flushes) u fizičku log datoteku na disku prije nego što se stvarne stranice podataka ažuriraju u datotekama baze podataka (MDF/NDF). Ovo garantuje ACID (Atomicity, Consistency, Isolation, Durability) usklađenost, osiguravajući da u slučaju pada sistema, SQL Server može ponoviti (roll forward) ili poništiti (roll back) transakcije.

Virtualne log datoteke (VLF) i kružno logovanje

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

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

Skraćivanje loga (Truncation) vs. Smanjivanje loga (Shrinking)

Česta zabluda je da skraćivanje loga smanjuje fizičku veličinu datoteke.
* Skraćivanje loga (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 loga (Log Shrinking): Proces fizičkog smanjenja veličine LDF datoteke i vraćanja prostora operativnom sistemu.

U modelu potpunog oporavka (Full Recovery model), skraćivanje loga se dešava samo kada je sigurnosna kopija (backup) transakcijskog loga uspješno završena (pod pretpostavkom da nijedan drugi proces ne drži log aktivnim).

Dijagnosticiranje greške “Transakcijski log pun” (Greška 9002)

Kada je log pun, vaš prvi korak nije slijepo dodavanje prostora na disku ili smanjivanje datoteka. Morate identifikovati zašto se log ne može skratiti. SQL Server pruža ugrađeni mehanizam da vam tačno kaže šta sprečava ponovnu upotrebu loga putem sys.databases kataloškog prikaza.

Pokrenite sljedeću T-SQL komandu da identifikujete 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 logova koristeći:

DBCC SQLPERF(LOGSPACE);

Uobičajena log_reuse_wait_desc stanja

  1. LOG_BACKUP: Baza podataka je u modelu potpunog (Full) ili Bulk-Logged oporavka, a sigurnosna kopija transakcijskog loga nije nedavno napravljena. Ovo je najčešći uzrok.
  2. ACTIVE_TRANSACTION: Transakcija koja dugo traje (npr. masivna rekonstrukcija indeksa ili zaboravljena nepotvrđena transakcija) drži log 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 Availability grupi, sekundarna replika je isključena ili se presporo sinhronizuje, prisiljavajući primarnu repliku da zadrži zapise loga 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 hitnu situaciju će varirati. Evo strategija brzog oporavka za najčešće scenarije.

Scenario 1: Nedostajuće ili neuspjele sigurnosne kopije loga (LOG_BACKUP)

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

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

Kada se backup završi, neaktivni VLF-ovi će biti skraćeni i SQL Server će nastaviti normalan rad. Ako je vaš backup disk pun, možda ćete morati napraviti backup na privremeni mrežni dijeljeni resurs ili null uređaj (izuzetno se ne preporučuje osim ako se baza podataka lako može reproducirati, jer prekida lanac loga):

-- UPOZORENJE: Ovo prekida lanac loga i ugrožava oporavak do određene tačke u vremenu (point-in-time recovery).
-- Koristite samo ako je apsolutno neophodno i odmah nakon toga uradite FULL backup.
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';

Scenario 2: Aktivne transakcije koje dugo traju (ACTIVE_TRANSACTION)

Ako jedna transakcija traje satima, ona sprečava skraćivanje loga tokom cijelog trajanja. Prvo, identifikujte problematičnu transakciju:

DBCC OPENTRAN('YourDatabaseName');

Ova komanda vraća najstariju aktivnu transakciju i njen ID procesa servera (SPID). Možete prikupiti više detalja o tome šta SPID radi upitom dinamičkih upravljačkih prikaza (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 zaglavljeni proces, možda ćete morati da ga prekinete da biste oslobodili log.

KILL <SPID>;

Napomena: Prekidanje masivne transakcije će pokrenuti poništavanje (rollback), što može potrajati značajno vrijeme i privremeno će generisati dodatnu aktivnost loga. Nemojte ponovo pokretati SQL Server servis tokom poništavanja, inače će baza podataka ući u režim oporavka nakon ponovnog pokretanja.

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

Ako je LDF datoteka potrošila cijeli disk, ne možete čak ni pokrenuti backup jer SQL Serveru treba mala količina prostora u logu da zabilježi sam događaj backupa. U ovom scenariju, morate dodati sekundarnu log datoteku na drugom disku sa 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 loga, ispraznite sekundarnu log datoteku i uklonite je:

-- 1. Napravite log backup da skratite log
BACKUP LOG [YourDatabaseName] TO DISK = '...';

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

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

Najbolje prakse za prevenciju i upravljanje transakcijskim logom

Reaktivno rješavanje problema je stresno i utiče na SLA. Implementacija proaktivnih arhitektonskih i operativnih najboljih praksi je ključna za stabilnost baze podataka u preduzeću.

1. Implementirajte robusnu, automatizovanu strategiju sigurnosnog kopiranja

Ako je baza podataka u modelu potpunog (Full) oporavka, česte sigurnosne kopije transakcijskog loga su obavezne. Ovisno o vašem cilju tačke oporavka (RPO) i obimu transakcija, backupi loga bi se trebali dešavati svakih 5 do 15 minuta.

Enterprise backup rješenja kao što je CloudSave značajno pojednostavljuju ovaj proces. Direktnom integracijom sa SQL Serverom putem VDI (Virtual Device Interface), CloudSave omogućava DBA-ovima da konfigurišu visokofrekventne backupove transakcijskog loga vođene politikama. Ovo osigurava da se logovi kontinuirano skraćuju, sigurno šifruju i pohranjuju van lokacije ili u nepromjenjivu cloud pohranu, sprečavajući stanje čekanja LOG_BACKUP bez potrebe za složenim prilagođenim SQL Agent poslovima.

2. Pravilno dimenzionišite transakcijski log i upravljajte VLF-ovima

Oslanjanje na automatsko povećanje (auto-growth) za upravljanje veličinom vašeg transakcijskog loga je opasan anti-uzorak. Operacije automatskog povećanja su skupe i pauziraju obradu transakcija dok se disk inicijalizuje nulama (osim ako je omogućena Instant File Initialization, što se ne odnosi na log datoteke).

Štaviše, česta, mala automatska povećanja (npr. povećanje za 10% ili 50MB odjednom) dovode do VLF fragmentacije. Transakcijski log sa hiljadama malih VLF-ova će ozbiljno degradirati vrijeme pokretanja baze podataka, performanse backupa i latenciju replikacije.

  • Prethodno dimenzionišite log: Analizirajte svoje najveće operacije održavanja (poput rekonstrukcije indeksa) i prethodno dimenzionišite LDF datoteku da ih prihvati bez rasta.
  • Postavite fiksno automatsko povećanje: Promijenite automatsko povećanje iz procenta u fiksnu veličinu (npr. 1GB ili 5GB) kako biste osigurali da se VLF-ovi kreiraju u zdravoj veličini.

Možete provjeriti svoj broj VLF-ova koristeći sljedeći upit (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 preko 500, razmislite o čekanju na miran period, smanjivanju loga na minimalnu veličinu i ručnom povećanju nazad na potrebnu veličinu u velikim komadima.

3. Optimizujte operacije održavanja indeksa

Rekonstrukcije indeksa su operacije koje se u potpunosti loguju, čak i u modelu Bulk-Logged oporavka (ovisno o tipu indeksa). Rekonstrukcija indeksa od 500GB će generisati najmanje 500GB zapisa transakcijskog loga.

Da biste ublažili pretrpanost loga tokom održavanja:
* Koristite SORT_IN_TEMPDB = ON prilikom rekonstrukcije indeksa. Ovo prebacuje fazu sortiranja u TempDB, smanjujući opterećenje na transakcijski log korisničke baze podataka.
* Prebacite se sa rekonstrukcije indeksa na reorganizaciju indeksa gdje je to moguće, jer su reorganizacije efikasnije u pogledu loga i mogu se prekinuti bez poništavanja cijele operacije.
* Grupisano izvršavajte velike DELETE ili UPDATE operacije. Umjesto brisanja 10 miliona redova u jednoj transakciji, brišite ih u komadima od 50.000, potvrđujući (committing) i omogućavajući backupima loga da skrate log između grupa.

4. Nadgledajte visoku dostupnost i topologije replikacije

U AlwaysOn Availability grupama, primarna replika ne može skratiti svoj log dok se zapisi loga ne potvrde na svim sinhronim i asinhronim sekundarnim replikama.

Ako sekundarna replika ode van mreže, ili ako mrežni propusni opseg ne može pratiti brzinu generisanja transakcija primarne replike, red čekanja za slanje (send queue) primarne replike će rasti, a log će se popuniti (tip čekanja AVAILABILITY_REPLICA).

Implementirajte robusno nadgledanje za SQLServer:Replica > Log Send Queue brojač performansi. Ako je sekundarna replika trajno izgubljena, morate je ukloniti iz Availability grupe ili obustaviti kretanje podataka kako biste omogućili skraćivanje primarnog loga.

Zaključak

Susret sa punim transakcijskim logom je obred prolaska za administratore baza podataka, ali ne mora rezultirati produženim zastojem. Razumijevanjem mehanike Write-Ahead Logging-a i VLF-ova, možete brzo dijagnosticirati osnovni uzrok koristeći sys.databases i primijeniti ispravnu strategiju brzog oporavka.

Dugoročna stabilnost se oslanja na odmak od reaktivnih popravki. Prethodno dimenzionisanje vaših log datoteka, optimizacija rutina održavanja i korištenje enterprise platformi za backup kao što je CloudSave za provođenje strogih, automatizovanih rasporeda backupa loga osigurat će da vaši transakcijski logovi ostanu zdravi, skraćeni i spremni za podršku produkcijskim radnim opterećenjima visokog propusnog opsega.

Kategorije