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.

Andmebaasiadministraatorite (DBA) ja DevOps-inseneride jaoks, kes haldavad Microsoft SQL Serverit, on vähe hoiatusi, mis tekitavad nii palju kohest ärevust kui viga 9002: The transaction log for database ‘X’ is full (Andmebaasi ‘X’ tehingulogi on täis). Kui tehingulogi saab täis ja ei saa enam kasvada, muutub andmebaas sisuliselt kirjutuskaitstuks. Kõik INSERT, UPDATE ja DELETE toimingud peatuvad, rakenduste tehingud ebaõnnestuvad ja tootmine seiskub.

SQL Serveri tehingulogi arhitektuuri mõistmine, algpõhjuse täpne diagnoosimine ja kiire taastamisprotseduuride läbiviimine on kriitilise tähtsusega oskused kõrge kättesaadavuse säilitamiseks. See põhjalik juhend uurib tehingulogi toimimist, kuidas lahendada täis logi hädaolukorras ja millised on arhitektuursed parimad tavad, et vältida selle kordumist.

SQL Serveri tehingulogi arhitektuuri mõistmine

Tehingulogi täitumise tõhusaks tõrkeotsinguks peate esmalt mõistma, kuidas SQL Server andmeid kirjutab ja haldab.

Write-Ahead Logging (WAL)

SQL Server kasutab Write-Ahead Logging (WAL) protokolli. Alati, kui toimub andmete muutmine, kirjutatakse muudatus esmalt mälus olevasse tehingulogi ja seejärel tühjendatakse kettal asuvasse füüsilisse logifaili, enne kui tegelikud andmelehed andmebaasifailides (MDF/NDF) uuendatakse. See tagab ACID-vastavuse (aatomilisus, järjepidevus, isolatsioon, vastupidavus), tagades, et krahhi korral saab SQL Server tehinguid uuesti esitada (roll forward) või tühistada (roll back).

Virtuaalsed logifailid (VLF) ja ringlogimine

Sisemiselt on füüsiline tehingulogifail (LDF) jagatud väiksemateks loogilisteks segmentideks, mida nimetatakse virtuaalseteks logifailideks (VLF). Tehingulogi töötab ringikujuliselt. Logikirjete kirjutamisel täidavad need ühe VLF-i ja liiguvad järgmise juurde.

Kui logi jõuab füüsilise faili lõppu, üritab see naasta algusesse. Siiski saab see VLF-i üle kirjutada ainult siis, kui see VLF on märgitud mitteaktiivseks. Kui kõik VLF-id on aktiivsed (mis tähendab, et need sisaldavad logikirjeid, mida SQL Server veel vajab), ei saa logi ringi käia. Kui automaatne kasv (auto-growth) on lubatud ja kettaruumi on saadaval, siis füüsiline fail kasvab. Kui ketas on täis või automaatne kasv on piiratud, kohtate viga 9002.

Logi kärpimine (Truncation) vs. logi vähendamine (Shrinking)

Levinud eksiarvamus on, et logi kärpimine vähendab füüsilise faili suurust.
* Logi kärpimine (Log Truncation): Protsess, mille käigus märgitakse aktiivsed VLF-id mitteaktiivseteks, muutes ruumi uuesti kasutatavaks. See ei vähenda LDF-faili suurust kettal.
* Logi vähendamine (Log Shrinking): Protsess, mille käigus vähendatakse füüsiliselt LDF-faili suurust ja tagastatakse ruum operatsioonisüsteemile.

Full Recovery mudelis toimub logi kärpimine ainult siis, kui tehingulogi varundamine on edukalt lõpule viidud (eeldusel, et ükski teine protsess ei hoia logi aktiivsena).

Veateate „Transaction Log Full“ (viga 9002) diagnoosimine

Kui logi on täis, ei ole teie esimene samm pimesi kettaruumi lisamine või failide vähendamine. Peate tuvastama, miks logi ei saa kärpida. SQL Server pakub sisseehitatud mehhanismi, et öelda täpselt, mis takistab logi taaskasutamist sys.databases kataloogivaate kaudu.

Kitsaskoha tuvastamiseks käivitage järgmine T-SQL käsk:

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

Samuti saate kontrollida oma tehingulogide praegust ruumikasutust, kasutades käsku:

DBCC SQLPERF(LOGSPACE);

Levinud log_reuse_wait_desc olekud

  1. LOG_BACKUP: Andmebaas on Full või Bulk-Logged taastamismudelis ja tehingulogi varundamist pole hiljuti tehtud. See on kõige levinum põhjus.
  2. ACTIVE_TRANSACTION: Kauakestev tehing (nt massiivne indeksi ülesehitamine või unustatud kinnitamata tehing) hoiab logi aktiivsena.
  3. REPLICATION / CDC: Transactional Replication või Change Data Capture (CDC) on lubatud ja Log Reader Agent pole veel tehinguid töödelnud.
  4. AVAILABILITY_REPLICA: AlwaysOn Availability Groupi puhul on sekundaarne replika lahti ühendatud või sünkroonib liiga aeglaselt, sundides primaarset replikat logikirjeid säilitama, kuni need on sekundaarsel replikal kinnistatud.

Kiire taastamise strateegiad: probleemi lahendamine tootmiskeskkonnas

Sõltuvalt tagastatud log_reuse_wait_desc väärtusest on teie hädaolukorra lahendus erinev. Siin on kiired taastamisstrateegiad kõige levinumate stsenaariumide jaoks.

Stsenaarium 1: Puuduvad või ebaõnnestunud logivarundused (LOG_BACKUP)

Kui oote tüüp on LOG_BACKUP, on lahendus lihtne: peate tehingulogi varundama.

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

Kui varundamine on lõpetatud, kärbitakse mitteaktiivsed VLF-id ja SQL Server jätkab tavapärast tööd. Kui teie varundusketas on täis, peate võib-olla varundama ajutisele võrgukettale või null-seadmesse (väga mittesoovitatav, kui andmebaas pole kergesti taastatav, kuna see lõhub logiahela):

-- HOIATUS: See lõhub logiahela ja kahjustab ajapunkti taastamist.
-- Kasutage ainult siis, kui see on hädavajalik, ja tehke kohe pärast seda FULL varundus.
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';

Stsenaarium 2: Kauakestvad aktiivsed tehingud (ACTIVE_TRANSACTION)

Kui üks tehing on kestnud tunde, takistab see logi kärpimist kogu selle aja jooksul. Esmalt tuvastage probleemne tehing:

DBCC OPENTRAN('YourDatabaseName');

See käsk tagastab vanima aktiivse tehingu ja selle serveriprotsessi ID (SPID). Lisateavet selle kohta, mida SPID teeb, saate dünaamiliste haldusvaadete (DMV) kaudu:

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

Kui tehing on pahatahtlik päring või seiskunud protsess, peate võib-olla selle logi vabastamiseks katkestama.

KILL <SPID>;

Märkus: Massiivse tehingu katkestamine käivitab tagasipööramise (rollback), mis võib võtta märkimisväärselt aega ja tekitab ajutiselt täiendavat logitegevust. Ärge taaskäivitage SQL Serveri teenust tagasipööramise ajal, vastasel juhul läheb andmebaas taaskäivitamisel taasterežiimi.

Stsenaarium 3: Hädaolukorra ruumieraldus (ketas on 100% täis)

Kui LDF-fail on hõivanud kogu ketta, ei saa te isegi varundust käivitada, sest SQL Server vajab varundussündmuse enda salvestamiseks veidi logiruumi. Sellisel juhul peate lisama sekundaarse logifaili teisele kettale, kus on vaba ruumi.

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

See annab SQL Serverile koheselt hingamisruumi. Kui andmebaas on võrgus, tehke tehingulogi varundus, tühjendage sekundaarne logifail ja eemaldage see:

-- 1. Tehke logi varundus, et logi kärpida
BACKUP LOG [YourDatabaseName] TO DISK = '...';

-- 2. Tühjendage ajutine logifail
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);

-- 3. Eemaldage ajutine logifail
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];

Parimad tavad tehingulogi ennetamiseks ja haldamiseks

Reaktiivne tõrkeotsing on stressirohke ja mõjutab teenusetaseme lepinguid (SLA). Ennetavate arhitektuursete ja operatiivsete parimate tavade rakendamine on ettevõtte andmebaasi stabiilsuse jaoks hädavajalik.

1. Rakendage tugev ja automatiseeritud varundusstrateegia

Kui andmebaas on Full taastamismudelis, on sagedased tehingulogi varundused kohustuslikud. Sõltuvalt teie taastepunkti eesmärgist (RPO) ja tehingute mahust peaksid logivarundused toimuma iga 5–15 minuti järel.

Ettevõtte varunduslahendused, nagu CloudSave, lihtsustavad seda protsessi märkimisväärselt. Integreerudes otse SQL Serveriga VDI (Virtual Device Interface) kaudu, võimaldab CloudSave DBA-del konfigureerida poliitikapõhiseid, suure sagedusega tehingulogi varundusi. See tagab, et logid kärbitakse pidevalt, krüpteeritakse turvaliselt ja salvestatakse väljaspool asukohta või muutumatusse pilvesalvestusse, vältides LOG_BACKUP ooteolekut ilma keeruliste kohandatud SQL Agent tööde vajaduseta.

2. Määrake tehingulogile õige suurus ja hallake VLF-e

Automaatsele kasvule lootmine tehingulogi suuruse haldamisel on ohtlik anti-muster. Automaatse kasvu toimingud on kulukad ja peatavad tehingute töötlemise, kuni ketas on nullitud (välja arvatud juhul, kui on lubatud Instant File Initialization, mis ei kehti logifailidele).

Lisaks põhjustavad sagedased väikesed automaatsed kasvud (nt 10% või 50 MB kaupa) VLF-i fragmenteerumist. Tehingulogi, milles on tuhandeid pisikesi VLF-e, halvendab märgatavalt andmebaasi käivitusaega, varundamise jõudlust ja replikatsiooni latentsust.

  • Määrake logi suurus ette: Analüüsige oma suurimaid hooldustoiminguid (nagu indeksite ülesehitamine) ja määrake LDF-faili suurus ette, et need mahuksid ära ilma kasvuta.
  • Määrake fikseeritud automaatne kasv: Muutke automaatne kasv protsendist fikseeritud suuruseks (nt 1 GB või 5 GB), et tagada VLF-ide loomine tervislikus suuruses.

Saate kontrollida oma VLF-ide arvu järgmise päringuga (SQL Server 2017+ puhul):

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

Kui teie VLF-ide arv on üle 500, kaaluge vaikse perioodi ootamist, logi vähendamist minimaalse suuruseni ja selle käsitsi suurendamist vajaliku suuruseni suurte tükkidena.

3. Optimeerige indeksi hooldustoiminguid

Indeksite ülesehitamine on täielikult logitud toiming, isegi Bulk-Logged taastamismudelis (sõltuvalt indeksi tüübist). 500 GB indeksi ülesehitamine tekitab vähemalt 500 GB tehingulogi kirjeid.

Logi paisumise leevendamiseks hoolduse ajal:
* Kasutage indeksite ülesehitamisel SORT_IN_TEMPDB = ON. See suunab sortimisfaasi TempDB-sse, vähendades koormust andmebaasi tehingulogile.
* Võimalusel vahetage indeksi ülesehitamine (rebuild) indeksi korrastamise (reorganize) vastu, kuna korrastamine on logi seisukohalt tõhusam ja seda saab katkestada ilma kogu toimingut tagasi pööramata.
* Partitsioneerige suured DELETE või UPDATE toimingud. Selle asemel, et kustutada 10 miljonit rida ühe tehinguga, kustutage need 50 000-kaupa, kinnitades (commit) ja lubades logivarundustel logi partiide vahel kärpida.

4. Jälgige kõrge kättesaadavuse ja replikatsiooni topoloogiaid

AlwaysOn Availability Groupides ei saa primaarne replika oma logi kärpida enne, kui logikirjed on kinnistatud kõigil sünkroonsetel ja asünkroonsetel sekundaarsetel replikatel.

Kui sekundaarne replika läheb võrguühenduseta või kui võrgu ribalaius ei suuda sammu pidada primaarse tehingute genereerimise kiirusega, kasvab primaarse replika saatmisjärjekord ja logi täitub (AVAILABILITY_REPLICA oote tüüp).

Rakendage tugev jälgimine SQLServer:Replica > Log Send Queue jõudlusloenduri jaoks. Kui sekundaarne replika on jäädavalt kadunud, peate selle Availability Groupist eemaldama või andmete liikumise peatama, et võimaldada primaarse logi kärpimist.

Kokkuvõte

Täis tehingulogiga kokkupuutumine on andmebaasiadministraatorite jaoks tavapärane katsumus, kuid see ei pea lõppema pikaajalise seisakuga. Mõistes Write-Ahead Loggingi ja VLF-ide toimimist, saate kiiresti diagnoosida algpõhjuse, kasutades sys.databases vaadet, ja rakendada õiget kiiret taastamisstrateegiat.

Pikaajaline stabiilsus sõltub reaktiivsetest parandustest loobumisest. Logifailide suuruse ette määramine, hooldusrutiinide optimeerimine ja ettevõtte tasemel varundusplatvormide, nagu CloudSave, kasutamine rangete ja automatiseeritud logivarundusgraafikute jõustamiseks tagab, et teie tehingulogid püsivad terved, kärbitud ja valmis toetama suure läbilaskevõimega tootmiskoormusi.

Rubriigid