MSSQL transakciju žurnāls ir pilns: novēršanas un ātras atkopšanas stratēģijas

Datu bāzu administratoriem (DBA) un DevOps inženieriem, kas pārvalda Microsoft SQL Server, ir maz brīdinājumu, kas izraisa tik lielu tūlītēju trauksmi kā 9002. kļūda: Datu bāzes ‘X’ transakciju žurnāls ir pilns. Kad transakciju žurnāls aizpildās un nevar palielināties, datu bāze faktiski kļūst tikai lasāma. Visas INSERT, UPDATE un DELETE darbības apstājas, lietojumprogrammu transakcijas neizdodas, un ražošanas process apstājas.

Izpratne par SQL Server transakciju žurnāla arhitektūru, precīza pamatcēloņa diagnosticēšana un ātra atkopšanas procedūru izpilde ir kritiski svarīgas prasmes augstas pieejamības uzturēšanai. Šajā visaptverošajā rokasgrāmatā ir apskatīti transakciju žurnāla mehānismi, kā atrisināt žurnāla pārpildes problēmu ārkārtas situācijā un arhitektūras labākā prakse, lai novērstu tās atkārtošanos.

Izpratne par SQL Server transakciju žurnāla arhitektūru

Lai efektīvi novērstu pilna transakciju žurnāla problēmas, vispirms ir jāsaprot, kā SQL Server raksta un pārvalda datus.

Iepriekšējas rakstīšanas žurnāls (Write-Ahead Logging — WAL)

SQL Server izmanto iepriekšējas rakstīšanas žurnāla (WAL) protokolu. Ikreiz, kad notiek datu modifikācija, izmaiņas vispirms tiek ierakstītas transakciju žurnālā atmiņā, pēc tam izvadītas uz fizisko žurnāla failu diskā, pirms faktiskās datu lapas tiek atjauninātas datu bāzes failos (MDF/NDF). Tas garantē ACID (atomitātes, konsekvences, izolācijas, izturības) atbilstību, nodrošinot, ka avārijas gadījumā SQL Server var atkārtot (roll forward) vai atsaukt (roll back) transakcijas.

Virtuālie žurnāla faili (VLF) un cikliskā žurnālošana

Iekšēji fiziskais transakciju žurnāla fails (LDF) ir sadalīts mazākos, loģiskos segmentos, ko sauc par virtuālajiem žurnāla failiem (VLF). Transakciju žurnāls darbojas cikliski. Kad žurnāla ieraksti tiek rakstīti, tie aizpilda vienu VLF un pāriet uz nākamo.

Kad žurnāls sasniedz fiziskā faila beigas, tas mēģina atgriezties sākumā. Tomēr tas var pārrakstīt VLF tikai tad, ja šis VLF ir atzīmēts kā neaktīvs. Ja visi VLF ir aktīvi (tas nozīmē, ka tie satur žurnāla ierakstus, kas SQL Server joprojām ir nepieciešami), žurnāls nevar atgriezties sākumā. Ja ir iespējota automātiskā palielināšana un ir pieejama vieta diskā, fiziskais fails palielinās. Ja disks ir pilns vai automātiskā palielināšana ir ierobežota, jūs saskarsieties ar 9002. kļūdu.

Žurnāla saīsināšana (Truncation) pret žurnāla samazināšanu (Shrinking)

Izplatīts nepareizs uzskats ir tāds, ka žurnāla saīsināšana samazina fiziskā faila lielumu.
* Žurnāla saīsināšana (Log Truncation): Process, kurā aktīvie VLF tiek atzīmēti kā neaktīvi, padarot vietu pieejamu atkārtotai izmantošanai. Tas nesamazina LDF faila lielumu diskā.
* Žurnāla samazināšana (Log Shrinking): Process, kurā fiziski tiek samazināts LDF faila lielums un vieta tiek atdota operētājsistēmai.

Pilnas atkopšanas modelī (Full Recovery model) žurnāla saīsināšana notiek tikai tad, kad ir veiksmīgi pabeigta transakciju žurnāla dublēšana (pieņemot, ka neviens cits process neuztur žurnālu aktīvu).

“Transakciju žurnāls ir pilns” kļūdas (9002. kļūda) diagnosticēšana

Kad žurnāls ir pilns, pirmais solis nav akli pievienot vietu diskā vai samazināt failus. Jums ir jāidentificē, kāpēc žurnālu nevar saīsināt. SQL Server nodrošina iebūvētu mehānismu, lai precīzi pateiktu, kas neļauj atkārtoti izmantot žurnālu, izmantojot sys.databases kataloga skatu.

Izpildiet šo T-SQL komandu, lai identificētu vājo vietu:

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

Varat arī pārbaudīt pašreizējo transakciju žurnālu vietas izmantojumu, izmantojot:

DBCC SQLPERF(LOGSPACE);

Biežākie log_reuse_wait_desc stāvokļi

1. LOG_BACKUP: Datu bāze izmanto pilnas vai bulk-logged atkopšanas modeli, un transakciju žurnāla dublējums pēdējā laikā nav veikts. Tas ir visizplatītākais iemesls.
2. ACTIVE_TRANSACTION: Ilgstoša transakcija (piemēram, masveida indeksa atjaunošana vai aizmirsta neapstiprināta transakcija) uztur žurnālu aktīvu.
3. REPLICATION / CDC: Ir iespējota transakciju replikācija vai Change Data Capture (CDC), un Log Reader Agent vēl nav apstrādājis transakcijas.
4. AVAILABILITY_REPLICA: AlwaysOn Availability Group grupā sekundārā replika ir atvienota vai sinhronizējas pārāk lēni, piespiežot primāro repliku saglabāt žurnāla ierakstus, līdz tie ir nostiprināti sekundārajā replikā.

Ātrās atkopšanas stratēģijas: Problēmas risināšana ražošanas vidē

Atkarībā no atgrieztā log_reuse_wait_desc, jūsu ārkārtas reakcija atšķirsies. Šeit ir ātrās atkopšanas stratēģijas visizplatītākajiem scenārijiem.

1. scenārijs: Trūkstoši vai neizdevušies žurnāla dublējumi (LOG_BACKUP)

Ja gaidīšanas veids ir LOG_BACKUP, risinājums ir vienkāršs: jums ir jāveic transakciju žurnāla dublēšana.

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

Kad dublēšana ir pabeigta, neaktīvie VLF tiks saīsināti, un SQL Server atsāks normālu darbību. Ja jūsu dublējuma disks ir pilns, iespējams, būs jāveic dublēšana uz pagaidu tīkla koplietojumu vai null ierīci (ļoti nav ieteicams, ja vien datu bāze nav viegli reproducējama, jo tas pārtrauc žurnāla ķēdi):

-- BRĪDINĀJUMS: Tas pārtrauc žurnāla ķēdi un apdraud atkopšanu uz noteiktu laiku.
-- Izmantojiet tikai tad, ja tas ir absolūti nepieciešams, un nekavējoties veiciet PILNU dublējumu.
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';

2. scenārijs: Ilgstošas aktīvās transakcijas (ACTIVE_TRANSACTION)

Ja viena transakcija ir darbojusies stundām ilgi, tā novērš žurnāla saīsināšanu visu tās darbības laiku. Vispirms identificējiet problemātisko transakciju:

DBCC OPENTRAN('YourDatabaseName');

Šī komanda atgriež vecāko aktīvo transakciju un tās Server Process ID (SPID). Varat iegūt sīkāku informāciju par to, ko dara SPID, vaicājot dinamiskos pārvaldības skatus (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>;

Ja transakcija ir negodīgs vaicājums vai iestrēdzis process, iespējams, būs jāpārtrauc tas, lai atbrīvotu žurnālu.

KILL <SPID>;

Piezīme: Masīvas transakcijas pārtraukšana izraisīs atcelšanu (rollback), kas var aizņemt ievērojamu laiku un īslaicīgi radīs papildu žurnāla darbību. Restartēšanas laikā nepārtrauciet SQL Server pakalpojumu, pretējā gadījumā datu bāze pēc restartēšanas pāries atkopšanas režīmā.

3. scenārijs: Ārkārtas vietas piešķiršana (disks ir 100% pilns)

Ja LDF fails ir patērējis visu disku, jūs pat nevarat veikt dublēšanu, jo SQL Server ir nepieciešama neliela žurnāla vieta, lai ierakstītu pašu dublēšanas notikumu. Šajā scenārijā jums ir jāpievieno sekundārais žurnāla fails citā diskā, kurā ir pieejama vieta.

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

Tas nekavējoties nodrošina SQL Server ar vietu darbībai. Kad datu bāze ir tiešsaistē, veiciet transakciju žurnāla dublējumu, iztukšojiet sekundāro žurnāla failu un noņemiet to:

-- 1. Veiciet žurnāla dublējumu, lai saīsinātu žurnālu
BACKUP LOG [YourDatabaseName] TO DISK = '...';

-- 2. Iztukšojiet pagaidu žurnāla failu
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);

-- 3. Noņemiet pagaidu žurnāla failu
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];

Labākā prakse transakciju žurnāla novēršanai un pārvaldībai

Reaktīvā problēmu novēršana ir saspringta un ietekmē SLA. Proaktīvas arhitektūras un darbības labākās prakses ieviešana ir būtiska uzņēmuma datu bāzes stabilitātei.

1. Ieviesiet stabilu, automatizētu dublēšanas stratēģiju

Ja datu bāze izmanto pilnas atkopšanas modeli, bieža transakciju žurnāla dublēšana ir obligāta. Atkarībā no jūsu atkopšanas punkta mērķa (RPO) un transakciju apjoma, žurnāla dublējumiem jānotiek ik pēc 5 līdz 15 minūtēm.

Uzņēmuma dublēšanas risinājumi, piemēram, CloudSave, ievērojami vienkāršo šo procesu. Integrējoties tieši ar SQL Server, izmantojot VDI (Virtual Device Interface), CloudSave ļauj DBA konfigurēt uz politiku balstītus, augstas frekvences transakciju žurnāla dublējumus. Tas nodrošina, ka žurnāli tiek nepārtraukti saīsināti, droši šifrēti un glabāti ārpus vietnes vai nemainīgā mākoņkrātuvē, novēršot LOG_BACKUP gaidīšanas stāvokli bez nepieciešamības pēc sarežģītiem pielāgotiem SQL Agent darbiem.

2. Pareizi izmēriet transakciju žurnālu un pārvaldiet VLF

Paļaušanās uz automātisko palielināšanu, lai pārvaldītu transakciju žurnāla lielumu, ir bīstams pretmodelis. Automātiskās palielināšanas darbības ir dārgas un aptur transakciju apstrādi, kamēr disks tiek inicializēts ar nullēm (ja vien nav iespējota tūlītēja faila inicializācija, kas neattiecas uz žurnāla failiem).

Turklāt bieža, maza automātiskā palielināšana (piemēram, palielināšana par 10% vai 50 MB vienlaikus) noved pie VLF fragmentācijas. Transakciju žurnāls ar tūkstošiem mazu VLF ievērojami pasliktinās datu bāzes startēšanas laiku, dublēšanas veiktspēju un replikācijas latentumu.

• Iepriekš iestatiet žurnāla lielumu: Analizējiet savas lielākās uzturēšanas darbības (piemēram, indeksa atjaunošanu) un iepriekš iestatiet LDF faila lielumu, lai tās varētu veikt bez palielināšanas.
• Iestatiet fiksētu automātisko palielināšanu: Mainiet automātisko palielināšanu no procentiem uz fiksētu lielumu (piemēram, 1 GB vai 5 GB), lai nodrošinātu, ka VLF tiek izveidoti veselīgā lielumā.

Varat pārbaudīt savu VLF skaitu, izmantojot šādu vaicājumu (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'));

Ja jūsu VLF skaits pārsniedz 500, apsveriet iespēju pagaidīt klusāku periodu, samazināt žurnālu līdz minimālajam lielumam un manuāli palielināt to atpakaļ līdz vajadzīgajam lielumam lielos gabalos.

3. Optimizējiet indeksa uzturēšanas darbības

Indeksu atjaunošana ir pilnībā žurnālotas darbības, pat Bulk-Logged atkopšanas modelī (atkarībā no indeksa veida). 500 GB indeksa atjaunošana radīs vismaz 500 GB transakciju žurnāla ierakstu.

Lai mazinātu žurnāla piepūšanos uzturēšanas laikā:
* Izmantojiet SORT_IN_TEMPDB = ON, atjaunojot indeksus. Tas pārvirza kārtošanas fāzi uz TempDB, samazinot slodzi uz lietotāja datu bāzes transakciju žurnālu.
* Ja iespējams, pārslēdzieties no indeksa atjaunošanas uz indeksa reorganizāciju, jo reorganizācijas ir efektīvākas žurnāla izmantošanā un tās var pārtraukt, neatceļot visu darbību.
* Veiciet lielu DELETE vai UPDATE darbību pakešu apstrādi. Tā vietā, lai vienā transakcijā izdzēstu 10 miljonus rindu, izdzēsiet tās 50 000 rindu blokos, apstiprinot un ļaujot žurnāla dublējumiem saīsināt žurnālu starp pakešu apstrādēm.

4. Uzraugiet augstas pieejamības un replikācijas topoloģijas

AlwaysOn Availability Groups grupā primārā replika nevar saīsināt savu žurnālu, līdz žurnāla ieraksti ir nostiprināti visās sinhronajās un asinhronajās sekundārajās replikās.

Ja sekundārā replika kļūst bezsaistē vai ja tīkla joslas platums nespēj sekot līdzi primārās replikas transakciju ģenerēšanas ātrumam, primārās replikas sūtīšanas rinda pieaugs, un žurnāls aizpildīsies (AVAILABILITY_REPLICA gaidīšanas veids).

Ieviesiet stabilu uzraudzību SQLServer:Replica > Log Send Queue veiktspējas skaitītājam. Ja sekundārā replika ir neatgriezeniski zaudēta, tā ir jānoņem no Availability Group vai jāaptur datu pārvietošana, lai primārais žurnāls varētu saīsināties.

Secinājums

Saskaršanās ar pilnu transakciju žurnālu ir datu bāzu administratoru ikdiena, taču tai nav jābeidzas ar ilgstošu dīkstāvi. Izprotot iepriekšējas rakstīšanas žurnāla un VLF mehānismus, varat ātri diagnosticēt pamatcēloņus, izmantojot sys.databases, un piemērot pareizo ātrās atkopšanas stratēģiju.

Ilgtermiņa stabilitāte ir atkarīga no atteikšanās no reaktīviem labojumiem. Žurnāla failu iepriekšēja izmēra noteikšana, uzturēšanas rutīnu optimizēšana un uzņēmuma līmeņa dublēšanas platformu, piemēram, CloudSave, izmantošana, lai ieviestu stingrus, automatizētus žurnāla dublēšanas grafikus, nodrošinās, ka jūsu transakciju žurnāli paliek veselīgi, saīsināti un gatavi atbalstīt augstas caurlaidspējas ražošanas darba slodzes.