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.

Duomenų bazių administratoriams (DBA) ir „DevOps“ inžinieriams, prižiūrintiems „Microsoft SQL Server“, nedaug įspėjimų sukelia tiek daug staigaus nerimo, kiek 9002 klaida: Duomenų bazės „X“ operacijų žurnalas yra pilnas. Kai operacijų žurnalas užsipildo ir negali išsiplėsti, duomenų bazė faktiškai tampa tik skaitoma. Visos INSERT, UPDATE ir DELETE operacijos sustoja, programų operacijos nepavyksta, o gamybinė veikla visiškai paralyžiuojama.

Suprasti pagrindinę „SQL Server“ operacijų žurnalo architektūrą, tiksliai diagnozuoti pagrindinę priežastį ir atlikti greito atkūrimo procedūras yra kritiniai įgūdžiai norint išlaikyti aukštą pasiekiamumą. Šiame išsamiame vadove nagrinėjami operacijų žurnalo mechanizmai, būdai, kaip išspręsti pilno žurnalo problemą kritiniu atveju, ir architektūrinės geriausios praktikos, padedančios išvengti šios problemos ateityje.

„SQL Server“ operacijų žurnalo architektūros supratimas

Norėdami efektyviai šalinti pilno operacijų žurnalo problemas, pirmiausia turite suprasti, kaip „SQL Server“ rašo ir tvarko duomenis.

Išankstinio įrašymo žurnalizavimas (WAL)

„SQL Server“ naudoja išankstinio įrašymo žurnalizavimo (WAL) protokolą. Kai atliekamas duomenų pakeitimas, pakeitimas pirmiausia įrašomas į operacijų žurnalą atmintyje, o tada perkeliamas į fizinį žurnalo failą diske prieš atnaujinant faktinius duomenų puslapius duomenų bazės failuose (MDF/NDF). Tai garantuoja ACID (atomumo, nuoseklumo, izoliacijos, patvarumo) atitiktį, užtikrinant, kad įvykus avarijai „SQL Server“ galėtų pakartoti (roll forward) arba anuliuoti (roll back) operacijas.

Virtualūs žurnalo failai (VLF) ir ciklinis žurnalizavimas

Viduje fizinis operacijų žurnalo failas (LDF) yra padalintas į mažesnius, loginius segmentus, vadinamus virtualiais žurnalo failais (VLF). Operacijų žurnalas veikia cikliškai. Kai žurnalo įrašai įrašomi, jie užpildo vieną VLF ir pereina prie kito.

Kai žurnalas pasiekia fizinio failo pabaigą, jis bando grįžti į pradžią. Tačiau jis gali perrašyti VLF tik tuo atveju, jei tas VLF yra pažymėtas kaip neaktyvus. Jei visi VLF yra aktyvūs (tai reiškia, kad juose yra žurnalo įrašų, kurių „SQL Server“ vis dar reikia), žurnalas negali grįžti į pradžią. Jei įjungtas automatinis augimas ir yra laisvos vietos diske, fizinis failas padidėja. Jei diskas pilnas arba automatinis augimas apribotas, susiduriate su 9002 klaida.

Žurnalo sutrumpinimas (Truncation) vs. žurnalo suspaudimas (Shrinking)

Dažna klaidinga nuomonė, kad žurnalo sutrumpinimas sumažina fizinio failo dydį.
* Žurnalo sutrumpinimas (Log Truncation): Procesas, kurio metu aktyvūs VLF pažymimi kaip neaktyvūs, todėl vieta tampa prieinama pakartotiniam naudojimui. Tai nesumažina LDF failo dydžio diske.
* Žurnalo suspaudimas (Log Shrinking): Procesas, kurio metu fiziškai sumažinamas LDF failo dydis ir vieta grąžinama operacinei sistemai.

Naudojant „Full“ atkūrimo modelį, žurnalo sutrumpinimas įvyksta tik tada, kai sėkmingai atliekama operacijų žurnalo atsarginė kopija (darant prielaidą, kad jokie kiti procesai nelaiko žurnalo aktyvaus).

„Operacijų žurnalas pilnas“ klaidos (9002 klaida) diagnozavimas

Kai žurnalas pilnas, pirmas žingsnis nėra aklai didinti vietą diske ar mažinti failus. Turite nustatyti, kodėl žurnalas negali būti sutrumpintas. „SQL Server“ pateikia įmontuotą mechanizmą, leidžiantį tiksliai sužinoti, kas trukdo pakartotinai naudoti žurnalą per sys.databases katalogo rodinį.

Vykdykite šią T-SQL komandą, kad nustatytumėte kliūtį:

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

Taip pat galite patikrinti dabartinį operacijų žurnalų vietos naudojimą naudodami:

DBCC SQLPERF(LOGSPACE);

Dažnos log_reuse_wait_desc būsenos

  1. LOG_BACKUP: Duomenų bazė naudoja „Full“ arba „Bulk-Logged“ atkūrimo modelį, o operacijų žurnalo atsarginė kopija nebuvo daryta neseniai. Tai dažniausia priežastis.
  2. ACTIVE_TRANSACTION: Ilgai trunkanti operacija (pvz., didžiulis indekso atstatymas arba pamiršta neįsipareigojusi operacija) laiko žurnalą aktyvų.
  3. REPLICATION / CDC: Įjungta „Transactional Replication“ arba „Change Data Capture“ (CDC), o „Log Reader Agent“ dar neapdorojo operacijų.
  4. AVAILABILITY_REPLICA: „AlwaysOn Availability Group“ grupėje antrinė replika yra atjungta arba sinchronizuojasi per lėtai, todėl pirminė replika priversta saugoti žurnalo įrašus, kol jie bus patvirtinti antrinėje replikoje.

Greito atkūrimo strategijos: problemos sprendimas gamybinėje aplinkoje

Priklausomai nuo grąžinto log_reuse_wait_desc, jūsų skubus atsakas skirsis. Štai greito atkūrimo strategijos dažniausiems scenarijams.

1 scenarijus: Trūkstamos arba nepavykusios žurnalo atsarginės kopijos (LOG_BACKUP)

Jei laukimo tipas yra LOG_BACKUP, sprendimas paprastas: turite sukurti operacijų žurnalo atsarginę kopiją.

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

Kai atsarginė kopija bus baigta, neaktyvūs VLF bus sutrumpinti ir „SQL Server“ atnaujins įprastą veiklą. Jei jūsų atsarginių kopijų diskas pilnas, gali tekti daryti atsarginę kopiją į laikiną tinklo bendrinimą arba „null“ įrenginį (labai nerekomenduojama, nebent duomenų bazę lengva atkurti, nes tai nutraukia žurnalo grandinę):

-- ĮSPĖJIMAS: Tai nutraukia žurnalo grandinę ir pakenkia atkūrimui tam tikru laiku.
-- Naudokite tik tada, kai būtina, ir iškart po to atlikite PILNĄ atsarginę kopiją.
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';

2 scenarijus: Ilgai trunkančios aktyvios operacijos (ACTIVE_TRANSACTION)

Jei viena operacija vykdoma valandų valandas, ji neleidžia sutrumpinti žurnalo visą tą laiką. Pirmiausia nustatykite probleminę operaciją:

DBCC OPENTRAN('YourDatabaseName');

Ši komanda grąžina seniausią aktyvią operaciją ir jos serverio proceso ID (SPID). Galite surinkti daugiau informacijos apie tai, ką veikia SPID, užklausdami dinaminio valdymo rodinius (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>;

Jei operacija yra nepageidaujama užklausa arba užstrigęs procesas, gali tekti ją nutraukti, kad atlaisvintumėte žurnalą.

KILL <SPID>;

Pastaba: Didžiulės operacijos nutraukimas sukels anuliavimą (rollback), kuris gali užtrukti daug laiko ir laikinai sukurs papildomą žurnalo veiklą. Neperkraukite „SQL Server“ paslaugos anuliavimo metu, kitaip duomenų bazė po perkrovimo pereis į atkūrimo režimą.

3 scenarijus: Skubus vietos paskirstymas (diskas pilnas 100%)

Jei LDF failas užėmė visą diską, negalite net atlikti atsarginės kopijos, nes „SQL Server“ reikia šiek tiek žurnalo vietos pačiam atsarginės kopijos įvykiui įrašyti. Tokiu atveju turite pridėti antrinį žurnalo failą kitame diske, kuriame yra laisvos vietos.

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

Tai suteikia „SQL Server“ laisvės. Kai duomenų bazė veikia, atlikite operacijų žurnalo atsarginę kopiją, ištuštinkite antrinį žurnalo failą ir pašalinkite jį:

-- 1. Atlikite žurnalo atsarginę kopiją, kad sutrumpintumėte žurnalą
BACKUP LOG [YourDatabaseName] TO DISK = '...';

-- 2. Ištuštinkite laikiną žurnalo failą
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);

-- 3. Pašalinkite laikiną žurnalo failą
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];

Geriausios operacijų žurnalo prevencijos ir valdymo praktikos

Reaktyvus problemų šalinimas kelia stresą ir veikia SLA. Proaktyvių architektūrinių ir operacinių geriausių praktikų įgyvendinimas yra būtinas įmonės duomenų bazės stabilumui.

1. Įdiekite patikimą, automatizuotą atsarginių kopijų strategiją

Jei duomenų bazė naudoja „Full“ atkūrimo modelį, dažnos operacijų žurnalo atsarginės kopijos yra privalomos. Priklausomai nuo jūsų atkūrimo taško tikslo (RPO) ir operacijų apimties, žurnalo atsarginės kopijos turėtų būti daromos kas 5–15 minučių.

Įmonių atsarginių kopijų sprendimai, tokie kaip „CloudSave“, žymiai supaprastina šį procesą. Tiesiogiai integruodamasis su „SQL Server“ per VDI (Virtual Device Interface), „CloudSave“ leidžia DBA konfigūruoti politika pagrįstas, aukšto dažnio operacijų žurnalo atsargines kopijas. Tai užtikrina, kad žurnalai būtų nuolat trumpinami, saugiai užšifruojami ir saugomi ne vietoje arba nekintamoje debesies saugykloje, išvengiant LOG_BACKUP laukimo būsenos be sudėtingų pasirinktinių „SQL Agent“ užduočių.

2. Tinkamai nustatykite operacijų žurnalo dydį ir valdykite VLF

Pasikliovimas automatiniu augimu valdant operacijų žurnalo dydį yra pavojingas antipatternas. Automatinio augimo operacijos yra brangios ir pristabdo operacijų apdorojimą, kol diskas inicijuojamas nuliais (nebent įjungtas „Instant File Initialization“, kuris netaikomas žurnalo failams).

Be to, dažnas, mažas automatinis augimas (pvz., didinimas 10% arba 50 MB vienu metu) sukelia VLF fragmentaciją. Operacijų žurnalas su tūkstančiais mažų VLF smarkiai pablogins duomenų bazės paleidimo laiką, atsarginių kopijų našumą ir replikacijos vėlavimą.

  • Iš anksto nustatykite žurnalo dydį: Išanalizuokite didžiausias priežiūros operacijas (pvz., indeksų atstatymą) ir iš anksto nustatykite LDF failo dydį, kad jis galėtų jas priimti neaugdamas.
  • Nustatykite fiksuotą automatinį augimą: Pakeiskite automatinį augimą iš procentinės dalies į fiksuotą dydį (pvz., 1 GB arba 5 GB), kad užtikrintumėte, jog VLF būtų sukurti tinkamo dydžio.

Galite patikrinti savo VLF skaičių naudodami šią užklausą („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'));

Jei jūsų VLF skaičius viršija 500, apsvarstykite galimybę palaukti ramesnio laikotarpio, sumažinti žurnalą iki minimalaus dydžio ir rankiniu būdu padidinti jį iki reikiamo dydžio dideliais blokais.

3. Optimizuokite indeksų priežiūros operacijas

Indeksų atstatymas yra visiškai žurnalizuojamos operacijos, net ir naudojant „Bulk-Logged“ atkūrimo modelį (priklausomai nuo indekso tipo). 500 GB indekso atstatymas sugeneruos bent 500 GB operacijų žurnalo įrašų.

Norėdami sumažinti žurnalo išsipūtimą priežiūros metu:
* Naudokite SORT_IN_TEMPDB = ON atstatydami indeksus. Tai perkelia rūšiavimo etapą į „TempDB“, sumažinant naštą vartotojo duomenų bazės operacijų žurnalui.
* Kur įmanoma, pereikite nuo indeksų atstatymo prie indeksų reorganizavimo, nes reorganizavimas yra efektyvesnis žurnalo atžvilgiu ir gali būti nutrauktas neanuliuojant visos operacijos.
* Suskirstykite dideles DELETE arba UPDATE operacijas į paketus. Užuot ištrynę 10 milijonų eilučių vienoje operacijoje, ištrinkite jas 50 000 eilučių paketais, įsipareigodami (commit) ir leisdami žurnalo atsarginėms kopijoms sutrumpinti žurnalą tarp paketų.

4. Stebėkite aukšto pasiekiamumo ir replikacijos topologijas

„AlwaysOn Availability Groups“ grupėje pirminė replika negali sutrumpinti savo žurnalo, kol žurnalo įrašai nebus patvirtinti visose sinchroninėse ir asinchroninėse antrinėse replikose.

Jei antrinė replika atsijungia arba jei tinklo pralaidumas negali suspėti su pirminės replikos operacijų generavimo greičiu, pirminės replikos siuntimo eilė išaugs, o žurnalas užsipildys (AVAILABILITY_REPLICA laukimo tipas).

Įdiekite patikimą SQLServer:Replica > Log Send Queue našumo skaitiklio stebėjimą. Jei antrinė replika visam laikui prarasta, turite ją pašalinti iš „Availability Group“ arba sustabdyti duomenų perkėlimą, kad pirminis žurnalas galėtų būti sutrumpintas.

Išvada

Susidūrimas su pilnu operacijų žurnalu yra duomenų bazių administratorių „krikštas“, tačiau tai neturi baigtis ilgalaikiu prastovos laiku. Suprasdami išankstinio įrašymo žurnalizavimo ir VLF mechanizmus, galite greitai diagnozuoti pagrindinę priežastį naudodami sys.databases ir pritaikyti tinkamą greito atkūrimo strategiją.

Ilgalaikis stabilumas priklauso nuo atsisakymo nuo reaktyvių pataisymų. Išankstinis žurnalo failų dydžio nustatymas, priežiūros rutinų optimizavimas ir įmonės lygio atsarginių kopijų platformų, tokių kaip „CloudSave“, naudojimas griežtiems, automatizuotiems žurnalo atsarginių kopijų tvarkaraščiams užtikrins, kad jūsų operacijų žurnalai išliks sveiki, sutrumpinti ir pasirengę palaikyti didelio pralaidumo gamybinius darbo krūvius.

Kategorijos