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.

Microsoft SQL Server kudeatzen duten Datu-baseen Administratzaileentzat (DBA) eta DevOps ingeniarientzat, 9002 errorea baino antsietate handiagoa sortzen duen alerta gutxi daude: ‘X’ datu-baserako transakzio-erregistroa beteta dago. Transakzio-erregistroa betetzen denean eta hazteko aukerarik ez duenean, datu-basea irakurtzeko soilik bihurtzen da. INSERT, UPDATE eta DELETE eragiketa guztiak gelditu egiten dira, aplikazioen transakzioek huts egiten dute eta produkzioa erabat gelditzen da.

SQL Server-en transakzio-erregistroaren arkitektura ulertzea, erroaren kausa zehaztasunez diagnostikatzea eta berreskuratze-prozedura azkarrak exekutatzea ezinbesteko trebetasunak dira erabilgarritasun handia mantentzeko. Gida integral honek transakzio-erregistroaren mekanika aztertzen du, larrialdi batean erregistro betea nola konpondu eta berriro gerta ez dadin arkitektura-jardunbide egokienak zeintzuk diren azaltzen du.

SQL Server Transakzio-erregistroaren Arkitektura Ulertzea

Transakzio-erregistro betea modu eraginkorrean konpontzeko, lehenik eta behin SQL Server-ek datuak nola idatzi eta kudeatzen dituen ulertu behar duzu.

Write-Ahead Logging (WAL)

SQL Server-ek Write-Ahead Logging (WAL) protokoloa erabiltzen du. Datuen aldaketaren bat gertatzen den bakoitzean, aldaketa lehenik transakzio-erregistroan idazten da memorian, eta ondoren diskoko erregistro-fitxategi fisikora isurtzen da, datu-orriak datu-baseko fitxategietan (MDF/NDF) eguneratu aurretik. Honek ACID (Atomikotasuna, Kontsistentzia, Isolamendua, Iraunkortasuna) betetzea bermatzen du, SQL Server-ek kraskadura bat gertatuz gero transakzioak berriro exekutatu (aurrera egin) edo desegin (atzera egin) ditzakeela ziurtatuz.

Log Fitxategi Birtualak (VLF) eta Erregistro Zirkularra

Barnean, transakzio-erregistroaren fitxategi fisikoa (LDF) VLF (Virtual Log Files) izeneko segmentu logiko txikiagoetan banatuta dago. Transakzio-erregistroak zirkularki funtzionatzen du. Erregistro-lerroak idazten diren heinean, VLF bat bete eta hurrengora pasatzen dira.

Erregistroa fitxategi fisikoaren amaierara iristen denean, hasierara itzultzen saiatzen da. Hala ere, VLF bat gainidatzi dezake soilik VLF hori aktibo ez dagoela markatuta badago. VLF guztiak aktibo badaude (hau da, SQL Server-ek oraindik behar dituen erregistroak badituzte), erregistroak ezin du itzuli. Hazkunde automatikoa gaituta badago eta diskoan lekurik badago, fitxategi fisikoa hazi egiten da. Diskoa beteta badago edo hazkunde automatikoa mugatuta badago, 9002 errorea jasoko duzu.

Erregistroaren Truntzioa vs. Erregistroaren Murrizketa

Ohiko uste okerra da erregistroa moztea (truncation) fitxategiaren tamaina fisikoa murrizten duela.
* Erregistroaren Truntzioa: VLF aktiboak aktibo ez daudela markatzeko prozesua da, espazioa berrerabiltzeko eskuragarri jarriz. Honek ez du diskoko LDF fitxategiaren tamaina murrizten.
* Erregistroaren Murrizketa (Shrinking): LDF fitxategiaren tamaina fisikoki murrizteko eta espazioa sistema eragileari itzultzeko prozesua da.

Berreskuratze-eredu osoan (Full Recovery), erregistroaren truntzioa transakzio-erregistroaren babeskopia bat behar bezala amaitzen denean soilik gertatzen da (beste prozesurik erregistroa aktibo mantentzen ez badu).

“Transakzio-erregistroa beteta” Errorea Diagnostikatzea (9002 Errorea)

Erregistroa beteta dagoenean, zure lehen urratsa ez da itsu-itsuan disko-espazioa gehitzea edo fitxategiak murriztea. Erregistroak zergatik ezin duen truntzioa egin identifikatu behar duzu. SQL Server-ek mekanismo integratu bat eskaintzen du erregistroa berrerabiltzea zer galarazten ari den jakiteko, sys.databases katalogoaren ikuspegiaren bidez.

Exekutatu hurrengo T-SQL komandoa botila-lepoa identifikatzeko:

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

Zure transakzio-erregistroen uneko espazioaren erabilera ere egiaztatu dezakezu honako hau erabiliz:

DBCC SQLPERF(LOGSPACE);

Ohiko log_reuse_wait_desc Egoerak

  1. LOG_BACKUP: Datu-basea Berreskuratze-eredu Osoan edo Bulk-Logged ereduan dago, eta transakzio-erregistroaren babeskopia ez da duela gutxi egin. Hau da kausa ohikoena.
  2. ACTIVE_TRANSACTION: Denbora luzez exekutatzen ari den transakzio batek (adibidez, indizeen berreraikuntza masibo bat edo konprometitu gabeko transakzio ahaztu bat) erregistroa aktibo mantentzen du.
  3. REPLICATION / CDC: Transakzio-erreplikazioa edo Change Data Capture (CDC) gaituta daude, eta Log Reader Agent-ak oraindik ez ditu transakzioak prozesatu.
  4. AVAILABILITY_REPLICA: AlwaysOn Availability Group batean, bigarren mailako erreplika bat deskonektatuta dago edo oso poliki sinkronizatzen ari da, eta horrek lehen mailako erreplikari erregistroak gordetzera behartzen du bigarren mailakoan gogortu arte.

Berreskuratze Azkarreko Estrategiak: Arazoa Produkzioan Konpontzea

Itzulitako log_reuse_wait_desc-aren arabera, zure larrialdiko erantzuna aldatu egingo da. Hona hemen ohiko eszenatokietarako berreskuratze azkarreko estrategiak.

1. Eszenatokia: Erregistroen Babeskopiak falta dira edo huts egin dute (LOG_BACKUP)

Itxarote-mota LOG_BACKUP bada, irtenbidea zuzena da: transakzio-erregistroaren babeskopia egin behar duzu.

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

Babeskopia amaitu ondoren, VLF inaktiboak truntzioa egingo dira eta SQL Server-ek ohiko eragiketak berreskuratuko ditu. Zure babeskopia-unitatea beteta badago, baliteke sareko partekatze batera edo null gailu batera babeskopia egin behar izatea (oso gomendagarria ez dena, datu-basea erraz erreproduzigarria ez bada, erregistro-katea hausten duelako):

-- KONTUZ: Honek erregistro-katea hausten du eta puntu-denborako berreskurapena arriskuan jartzen du.
-- Erabili soilik guztiz beharrezkoa bada eta jarraitu berehala FULL babeskopia batekin.
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';

2. Eszenatokia: Denbora luzez exekutatzen ari diren transakzio aktiboak (ACTIVE_TRANSACTION)

Transakzio bakar batek orduak daramatzan exekutatzen, erregistroaren truntzioa eragozten du denbora osoan. Lehenik, identifikatu transakzioa:

DBCC OPENTRAN('YourDatabaseName');

Komando honek transakzio aktibo zaharrena eta bere Server Process ID (SPID) itzultzen ditu. SPID-ak zer egiten ari den buruzko xehetasun gehiago lor ditzakezu kudeaketa-ikuspegi dinamikoak (DMV) kontsultatuz:

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

Transakzioa kontsulta gaizto bat edo prozesu gelditu bat bada, erregistroa askatzeko hura amaitu behar izan dezakezu.

KILL <SPID>;

Oharra: Transakzio masibo bat hiltzeak atzera-egitea (rollback) eragingo du, denbora asko behar izan dezakeena eta aldi baterako erregistro-jarduera gehigarria sortuko duena. Ez berrabiarazi SQL Server zerbitzua atzera-egitean, bestela datu-basea berreskuratze-moduan sartuko da berrabiaraztean.

3. Eszenatokia: Larrialdiko Espazio Esleipena (Diskoa %100 beteta)

LDF fitxategiak unitate osoa kontsumitu badu, ezin duzu babeskopiarik ere egin, SQL Server-ek erregistro-espazio txiki bat behar duelako babeskopia-gertaera bera erregistratzeko. Eszenatoki honetan, erregistro-fitxategi sekundario bat gehitu behar duzu leku librea duen beste unitate batean.

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

Honek berehala ematen dio SQL Server-i arnasa hartzeko aukera. Datu-basea linean dagoenean, egin transakzio-erregistroaren babeskopia bat, hustu erregistro-fitxategi sekundarioa eta kendu:

-- 1. Egin erregistroaren babeskopia bat erregistroa truntzioa egiteko
BACKUP LOG [YourDatabaseName] TO DISK = '...';

-- 2. Hustu aldi baterako erregistro-fitxategia
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);

-- 3. Kendu aldi baterako erregistro-fitxategia
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];

Transakzio-erregistroa Prebenitzeko eta Kudeatzeko Jardunbide Egokiak

Arazoak konpontzea estresagarria da eta SLA-etan eragina du. Arkitektura eta operazio-jardunbide egokiak proaktiboki ezartzea ezinbestekoa da datu-baseen egonkortasunerako.

1. Babeskopia-estrategia Automatizatu eta Sendoa Ezartzea

Datu-basea Berreskuratze-eredu Osoan badago, transakzio-erregistroaren babeskopia maiz egitea derrigorrezkoa da. Zure Berreskuratze-puntuaren Helburuaren (RPO) eta transakzio-bolumenaren arabera, erregistroen babeskopiak 5 eta 15 minuturo egin beharko lirateke.

CloudSave bezalako babeskopia-soluzio korporatiboek prozesu hau asko errazten dute. SQL Server-ekin VDI (Virtual Device Interface) bidez zuzenean integratuz, CloudSave-k DBA-ei politika bidezko transakzio-erregistroen babeskopia maiztasun handikoak konfiguratzea ahalbidetzen die. Honek erregistroak etengabe truntzioa egiten direla, segurtasunez enkriptatuta eta kanpoan edo hodeiko biltegiratze aldaezinean gordetzen direla ziurtatzen du, LOG_BACKUP itxarote-egoera saihestuz SQL Agent lan konplexurik behar izan gabe.

2. Transakzio-erregistroa Tamaina Egokian Jarri eta VLFak Kudeatu

Transakzio-erregistroaren tamaina kudeatzeko hazkunde automatikoan oinarritzea eredu arriskutsua da. Hazkunde automatikoko eragiketak garestiak dira eta transakzioen prozesamendua pausatzen dute diskoa zeroz hasieratzen den bitartean (Instant File Initialization gaituta ez badago, eta hori ez zaie aplikatzen erregistro-fitxategiei).

Gainera, hazkunde automatiko txiki eta maiz (adibidez, %10 edo 50MB aldi bakoitzean) VLF fragmentazioa eragiten dute. Milaka VLF txiki dituen transakzio-erregistro batek datu-basearen abiarazte-denborak, babeskopien errendimendua eta erreplikazioaren latentzia larriki kaltetuko ditu.

  • Erregistroa aurrez tamainatu: Aztertu zure mantentze-eragiketa handienak (indizeen berreraikuntzak bezala) eta aurrez tamainatu LDF fitxategia hazi gabe haiei egokitzeko.
  • Ezarri hazkunde automatiko finkoa: Aldatu hazkunde automatikoa ehuneko batetik tamaina finko batera (adibidez, 1GB edo 5GB) VLFak tamaina osasuntsuan sortzen direla ziurtatzeko.

Zure VLF kopurua egiaztatu dezakezu hurrengo kontsulta erabiliz (SQL Server 2017+ bertsiorako):

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

Zure VLF kopurua 500etik gorakoa bada, kontuan hartu lasai dagoen aldi baten zain egotea, erregistroa tamaina minimora murriztea eta eskuz tamaina handitan behar den tamainara haztea.

3. Indizeen Mantentze-eragiketak Optimizatzea

Indizeen berreraikuntzak erabat erregistratutako eragiketak dira, baita Bulk-Logged berreskuratze-ereduan ere (indize motaren arabera). 500GB-ko indize bat berreraikitzeak gutxienez 500GB-ko transakzio-erregistroak sortuko ditu.

Mantentze-lanetan erregistroaren puztea arintzeko:
* Erabili SORT_IN_TEMPDB = ON indizeak berreraikitzean. Honek ordenatze-fasea TempDB-ra deskargatzen du, erabiltzailearen datu-basearen transakzio-erregistroaren zama murriztuz.
* Aldatu indizeen berreraikuntzetatik indizeen berrantolaketetara ahal den guztietan, berrantolaketak erregistroaren ikuspegitik eraginkorragoak direlako eta eten daitezkeelako eragiketa osoa atzera bota gabe.
* Batch-etan egin DELETE edo UPDATE eragiketa handiak. 10 milioi errenkada transakzio bakarrean ezabatu beharrean, ezabatu 50.000ko multzotan, konprometituz eta erregistroen babeskopiei batch-en artean erregistroa truntzioa egiten utziz.

4. Erabilgarritasun Handia eta Erreplikazio Topologiak Monitorizatzea

AlwaysOn Availability Groups-etan, lehen mailako erreplikak ezin du bere erregistroa truntzioa egin erregistro-lerroak bigarren mailako erreplika sinkrono eta asinkrono guztietan gogortu arte.

Bigarren mailako erreplika bat lineaz kanpo geratzen bada, edo sareko banda-zabalera lehen mailakoaren transakzio-sorkuntza abiadurari jarraitzeko gai ez bada, lehen mailakoaren bidalketa-ilara hazi egingo da eta erregistroa bete egingo da (AVAILABILITY_REPLICA itxarote-mota).

Ezarri monitorizazio sendoa SQLServer:Replica > Log Send Queue errendimendu-kontagailurako. Bigarren mailako erreplika bat betirako galtzen bada, Availability Group-etik kendu edo datuen mugimendua eten behar duzu lehen mailako erregistroak truntzioa egin dezan.

Ondorioa

Transakzio-erregistro betea aurkitzea datu-baseen administratzaileentzako pasabide bat da, baina ez du zertan geldialdi luzeak eragin behar. Write-Ahead Logging eta VLF-en mekanika ulertuz, erroaren kausa azkar diagnostikatu dezakezu sys.databases erabiliz eta berreskuratze azkarreko estrategia zuzena aplikatu.

Epe luzerako egonkortasuna konponketa erreaktiboetatik aldentzean datza. Zure erregistro-fitxategiak aurrez tamainatzeak, mantentze-errutinak optimizatzeak eta CloudSave bezalako babeskopia-plataforma korporatiboak erabiltzeak erregistroen babeskopia-egutegi zorrotz eta automatizatuak betearazteko, zure transakzio-erregistroak osasuntsu, truntzioa eginda eta errendimendu handiko produkzio-kargak onartzeko prest egongo direla ziurtatuko du.

Kategoriak