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.

Për Administratorët e Bazave të të Dhënave (DBA) dhe inxhinierët DevOps që menaxhojnë Microsoft SQL Server, pak alarme shkaktojnë aq ankth të menjëhershëm sa Gabimi 9002: Regjistri i transaksioneve për bazën e të dhënave ‘X’ është plot. Kur regjistri i transaksioneve mbushet dhe nuk mund të rritet, baza e të dhënave bëhet efektivisht vetëm për lexim. Të gjitha operacionet INSERT, UPDATE dhe DELETE ndalen, transaksionet e aplikacionit dështojnë dhe prodhimi ndalet plotësisht.

Kuptimi i arkitekturës themelore të regjistrit të transaksioneve të SQL Server, diagnostikimi i saktë i shkakut rrënjësor dhe ekzekutimi i procedurave të shpejta të rikuperimit janë aftësi kritike për ruajtjen e disponueshmërisë së lartë. Ky udhëzues gjithëpërfshirës eksploron mekanikën e regjistrit të transaksioneve, si të zgjidhni një regjistër të plotë në një emergjencë dhe praktikat më të mira arkitekturore për të parandaluar përsëritjen e kësaj situate.

Kuptimi i Arkitekturës së Regjistrit të Transaksioneve të SQL Server

Për të zgjidhur në mënyrë efektive një regjistër transaksionesh të plotë, së pari duhet të kuptoni se si SQL Server shkruan dhe menaxhon të dhënat.

Regjistrimi me Shkrim përpara (Write-Ahead Logging – WAL)

SQL Server përdor një protokoll të Regjistrimit me Shkrim përpara (WAL). Sa herë që ndodh një modifikim i të dhënave, ndryshimi shkruhet fillimisht në regjistrin e transaksioneve në memorie, pastaj derdhet në skedarin fizik të regjistrit në disk përpara se faqet aktuale të të dhënave të përditësohen në skedarët e bazës së të dhënave (MDF/NDF). Kjo garanton pajtueshmërinë ACID (Atomicitet, Konsistencë, Izolim, Qëndrueshmëri), duke siguruar që në rast të një përplasjeje, SQL Server mund të riprodhojë (roll forward) ose zhbëjë (roll back) transaksionet.

Skedarët Log Virtualë (VLFs) dhe Regjistrimi Rrethor

Brendësisht, skedari fizik i regjistrit të transaksioneve (LDF) është i ndarë në segmente më të vogla, logjike të quajtura Skedarë Log Virtualë (VLF). Regjistri i transaksioneve funksionon në mënyrë rrethore. Ndërsa regjistrimet e log-ut shkruhen, ato mbushin një VLF dhe kalojnë te tjetri.

Kur regjistri arrin në fund të skedarit fizik, ai përpiqet të kthehet në fillim. Megjithatë, ai mund të mbishkruajë një VLF vetëm nëse ai VLF është shënuar si joaktiv. Nëse të gjithë VLF-të janë aktivë (që do të thotë se ato përmbajnë regjistrime të log-ut që kërkohen ende nga SQL Server), regjistri nuk mund të mbyllet. Nëse rritja automatike (auto-growth) është e aktivizuar dhe hapësira në disk është e disponueshme, skedari fizik rritet. Nëse disku është plot ose rritja automatike është e kufizuar, ju hasni Gabimin 9002.

Trunkimi i Regjistrit kundrejt Zvogëlimit të Regjistrit

Një keqkuptim i zakonshëm është se trunkimi i regjistrit zvogëlon madhësinë fizike të skedarit.
* Trunkimi i Regjistrit (Log Truncation): Procesi i shënimit të VLF-ve aktive si joaktive, duke e bërë hapësirën të disponueshme për ripërdorim. Ai nuk zvogëlon madhësinë e skedarit LDF në disk.
* Zvogëlimi i Regjistrit (Log Shrinking): Procesi i zvogëlimit fizik të madhësisë së skedarit LDF dhe kthimit të hapësirës në sistemin operativ.

Në modelin e Rikuperimit të Plotë (Full Recovery), trunkimi i regjistrit ndodh vetëm kur një kopje rezervë (backup) e regjistrit të transaksioneve përfundohet me sukses (duke supozuar se asnjë proces tjetër nuk po e mban regjistrin aktiv).

Diagnostikimi i Gabimit “Regjistri i Transaksioneve është Plot” (Gabimi 9002)

Kur regjistri është plot, hapi juaj i parë nuk është të shtoni verbërisht hapësirë në disk ose të zvogëloni skedarët. Ju duhet të identifikoni pse regjistri nuk mund të trunkojë. SQL Server ofron një mekanizëm të integruar për t’ju treguar saktësisht se çfarë po pengon ripërdorimin e regjistrit përmes pamjes së katalogut sys.databases.

Ekzekutoni komandën e mëposhtme T-SQL për të identifikuar pengesën:

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

Ju gjithashtu mund të kontrolloni përdorimin aktual të hapësirës së regjistrave të transaksioneve duke përdorur:

DBCC SQLPERF(LOGSPACE);

Gjendjet e zakonshme të log_reuse_wait_desc

  1. LOG_BACKUP: Baza e të dhënave është në modelin e rikuperimit Full ose Bulk-Logged dhe një kopje rezervë e regjistrit të transaksioneve nuk është marrë kohët e fundit. Ky është shkaku më i zakonshëm.
  2. ACTIVE_TRANSACTION: Një transaksion që zgjat shumë (p.sh., një rindërtim masiv i indeksit ose një transaksion i harruar i pakonfirmuar) po e mban regjistrin aktiv.
  3. REPLICATION / CDC: Replikimi i transaksioneve ose Change Data Capture (CDC) është i aktivizuar dhe Agjenti i Lexuesit të Regjistrit (Log Reader Agent) nuk i ka përpunuar ende transaksionet.
  4. AVAILABILITY_REPLICA: Në një Grup Disponueshmërie AlwaysOn, një replikë dytësore është shkëputur ose sinkronizohet shumë ngadalë, duke detyruar replikën kryesore të mbajë regjistrimet e log-ut derisa ato të forcohen në replikën dytësore.

Strategjitë e Rikuperimit të Shpejtë: Zgjidhja e Problemit në Prodhim

Në varësi të log_reuse_wait_desc të kthyer, përgjigja juaj e urgjencës do të ndryshojë. Këtu janë strategjitë e rikuperimit të shpejtë për skenarët më të zakonshëm.

Skenari 1: Kopjet rezervë të regjistrit mungojnë ose dështojnë (LOG_BACKUP)

Nëse lloji i pritjes është LOG_BACKUP, zgjidhja është e thjeshtë: duhet të bëni një kopje rezervë të regjistrit të transaksioneve.

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

Pasi të përfundojë kopja rezervë, VLF-të joaktive do të trunkohen dhe SQL Server do të rifillojë operacionet normale. Nëse disku juaj i kopjeve rezervë është plot, mund t’ju duhet të bëni kopje rezervë në një ndarje rrjeti të përkohshme ose në një pajisje null (nuk rekomandohet fort përveç nëse baza e të dhënave është lehtësisht e riprodhueshme, pasi kjo prish zinxhirin e regjistrit):

-- KUJDES: Kjo prish zinxhirin e regjistrit dhe komprometon rikuperimin në një pikë kohore.
-- Përdoreni vetëm nëse është absolutisht e nevojshme dhe ndiqeni menjëherë me një kopje rezervë FULL.
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';

Skenari 2: Transaksionet aktive që zgjasin shumë (ACTIVE_TRANSACTION)

Nëse një transaksion i vetëm ka zgjatur për orë të tëra, ai parandalon trunkimin e regjistrit për të gjithë kohëzgjatjen. Së pari, identifikoni transaksionin problematik:

DBCC OPENTRAN('YourDatabaseName');

Kjo komandë kthen transaksionin më të vjetër aktiv dhe ID-në e Procesit të Serverit (SPID). Ju mund të mblidhni më shumë detaje rreth asaj që po bën SPID duke pyetur pamjet e menaxhimit dinamik (DMVs):

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

Nëse transaksioni është një pyetje e gabuar ose një proces i ngecur, mund t’ju duhet ta ndërprisni atë për të liruar regjistrin.

KILL <SPID>;

Shënim: Ndërprerja e një transaksioni masiv do të shkaktojë një rikthim (rollback), i cili mund të marrë një kohë të konsiderueshme dhe do të gjenerojë përkohësisht aktivitet shtesë të regjistrit. Mos e rinisni shërbimin e SQL Server gjatë një rikthimi, përndryshe baza e të dhënave do të hyjë në modalitetin e rikuperimit pas rinisjes.

Skenari 3: Alokimi i hapësirës së urgjencës (Disku është 100% plot)

Nëse skedari LDF ka konsumuar të gjithë diskun, nuk mund as të ekzekutoni një kopje rezervë sepse SQL Server kërkon një sasi të vogël hapësire regjistri për të regjistruar vetë ngjarjen e kopjes rezervë. Në këtë skenar, duhet të shtoni një skedar regjistri dytësor në një disk tjetër me hapësirë të disponueshme.

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

Kjo i siguron menjëherë SQL Server-it hapësirë për të marrë frymë. Pasi baza e të dhënave të jetë në linjë, bëni një kopje rezervë të regjistrit të transaksioneve, zbrazni skedarin dytësor të regjistrit dhe hiqeni atë:

-- 1. Bëni një kopje rezervë të regjistrit për të trunkuar regjistrin
BACKUP LOG [YourDatabaseName] TO DISK = '...';

-- 2. Zbrazni skedarin e përkohshëm të regjistrit
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);

-- 3. Hiqni skedarin e përkohshëm të regjistrit
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];

Praktikat më të mira për parandalimin dhe menaxhimin e regjistrit të transaksioneve

Zgjidhja reaktive e problemeve është stresuese dhe ndikon në SLA. Zbatimi i praktikave më të mira arkitekturore dhe operacionale proaktive është thelbësor për stabilitetin e bazës së të dhënave të ndërmarrjes.

1. Zbatoni një strategji të fuqishme dhe të automatizuar të kopjeve rezervë

Nëse një bazë të dhënash është në modelin e rikuperimit Full, kopjet rezervë të shpeshta të regjistrit të transaksioneve janë të detyrueshme. Në varësi të Objektivit tuaj të Pikës së Rikuperimit (RPO) dhe vëllimit të transaksioneve, kopjet rezervë të regjistrit duhet të ndodhin çdo 5 deri në 15 minuta.

Zgjidhjet e kopjeve rezervë të ndërmarrjeve si CloudSave e thjeshtojnë këtë proces ndjeshëm. Duke u integruar drejtpërdrejt me SQL Server përmes VDI (Virtual Device Interface), CloudSave u lejon DBA-ve të konfigurojnë kopje rezervë të regjistrit të transaksioneve me frekuencë të lartë, të drejtuara nga politika. Kjo siguron që regjistrat të trunkohen vazhdimisht, të enkriptohen në mënyrë të sigurt dhe të ruhen jashtë vendit ose në ruajtje cloud të pandryshueshme, duke parandaluar gjendjen e pritjes LOG_BACKUP pa pasur nevojë për punë komplekse të personalizuara të SQL Agent.

2. Përcaktoni madhësinë e duhur të regjistrit të transaksioneve dhe menaxhoni VLF-të

Mbështetja te rritja automatike për të menaxhuar madhësinë e regjistrit të transaksioneve është një anti-model i rrezikshëm. Operacionet e rritjes automatike janë të shtrenjta dhe ndalojnë përpunimin e transaksioneve ndërsa disku inicializohet me zero (përveç nëse është aktivizuar Inicializimi i Shpejtë i Skedarit, i cili nuk zbatohet për skedarët e regjistrit).

Për më tepër, rritjet automatike të shpeshta dhe të vogla (p.sh., rritja me 10% ose 50MB në të njëjtën kohë) çojnë në fragmentim të VLF-ve. Një regjistër transaksionesh me mijëra VLF të vogla do të degradojë rëndë kohën e nisjes së bazës së të dhënave, performancën e kopjeve rezervë dhe vonesën e replikimit.

  • Përcaktoni paraprakisht madhësinë e regjistrit: Analizoni operacionet tuaja më të mëdha të mirëmbajtjes (si rindërtimet e indekseve) dhe përcaktoni paraprakisht madhësinë e skedarit LDF për t’i akomoduar ato pa u rritur.
  • Vendosni rritje automatike fikse: Ndryshoni rritjen automatike nga një përqindje në një madhësi fikse (p.sh., 1GB ose 5GB) për të siguruar që VLF-të të krijohen në një madhësi të shëndetshme.

Ju mund të kontrolloni numrin tuaj të VLF-ve duke përdorur pyetjen e mëposhtme (për 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'));

Nëse numri juaj i VLF-ve është mbi 500, konsideroni të prisni për një periudhë të qetë, të zvogëloni regjistrin në një madhësi minimale dhe ta rritni manualisht përsëri në madhësinë e kërkuar në pjesë të mëdha.

3. Optimizoni operacionet e mirëmbajtjes së indekseve

Rindërtimet e indekseve janë operacione të regjistruara plotësisht, madje edhe në modelin e rikuperimit Bulk-Logged (në varësi të llojit të indeksit). Rindërtimi i një indeksi 500GB do të gjenerojë të paktën 500GB regjistrime të regjistrit të transaksioneve.

Për të zbutur fryrjen e regjistrit gjatë mirëmbajtjes:
* Përdorni SORT_IN_TEMPDB = ON kur rindërtoni indekset. Kjo shkarkon fazën e renditjes në TempDB, duke reduktuar barrën në regjistrin e transaksioneve të bazës së të dhënave të përdoruesit.
* Kaloni nga rindërtimi i indekseve në riorganizimin e indekseve ku është e mundur, pasi riorganizimet janë më efikase në regjistrim dhe mund të ndërpriten pa rikthyer të gjithë operacionin.
* Grumbulloni operacionet e mëdha DELETE ose UPDATE. Në vend që të fshini 10 milionë rreshta në një transaksion, fshini ato në grupe prej 50,000, duke konfirmuar dhe duke lejuar kopjet rezervë të regjistrit të trunkojnë regjistrin midis grupeve.

4. Monitoroni topologjitë e Disponueshmërisë së Lartë dhe Replikimit

Në Grupet e Disponueshmërisë AlwaysOn, replika kryesore nuk mund të trunkojë regjistrin e saj derisa regjistrimet e regjistrit të jenë forcuar në të gjitha replikat dytësore sinkrone dhe asinkrone.

Nëse një replikë dytësore shkon jashtë linje, ose nëse gjerësia e brezit të rrjetit nuk mund të përballojë shkallën e gjenerimit të transaksioneve të replikës kryesore, radha e dërgimit të replikës kryesore do të rritet dhe regjistri do të mbushet (lloji i pritjes AVAILABILITY_REPLICA).

Zbatoni monitorim të fuqishëm për numëruesin e performancës SQLServer:Replica > Log Send Queue. Nëse një replikë dytësore humbet përgjithmonë, duhet ta hiqni atë nga Grupi i Disponueshmërisë ose të pezulloni lëvizjen e të dhënave për të lejuar trunkimin e regjistrit kryesor.

Përfundim

Hasja e një regjistri transaksionesh të plotë është një rit kalimi për administratorët e bazave të të dhënave, por nuk duhet të rezultojë domosdoshmërisht në kohë joproduktive të zgjatur. Duke kuptuar mekanikën e Regjistrimit me Shkrim përpara dhe VLF-ve, mund të diagnostikoni shpejt shkakun rrënjësor duke përdorur sys.databases dhe të aplikoni strategjinë e duhur të rikuperimit të shpejtë.

Stabiliteti afatgjatë mbështetet në largimin nga rregullimet reaktive. Përcaktimi paraprak i madhësisë së skedarëve të regjistrit, optimizimi i rutinave të mirëmbajtjes dhe përdorimi i platformave të kopjeve rezervë të nivelit të ndërmarrjes si CloudSave për të zbatuar orare strikte dhe të automatizuara të kopjeve rezervë të regjistrit do të sigurojë që regjistrat tuaj të transaksioneve të mbeten të shëndetshëm, të trunkuar dhe gati për të mbështetur ngarkesat e punës së prodhimit me xhiro të lartë.

Kategori