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.

For databaseadministratorer (DBA-er) og DevOps-ingeniører som administrerer Microsoft SQL Server, er det få varsler som skaper så mye umiddelbar angst som Feil 9002: Transaksjonsloggen for databasen ‘X’ er full. Når transaksjonsloggen fylles opp og ikke kan vokse, blir databasen i praksis skrivebeskyttet. Alle INSERT-, UPDATE– og DELETE-operasjoner stopper opp, applikasjonstransaksjoner feiler, og produksjonen stopper helt opp.

Å forstå den underliggende arkitekturen til SQL Server-transaksjonsloggen, nøyaktig diagnostisering av rotårsaken og utførelse av raske gjenopprettingsprosedyrer er kritiske ferdigheter for å opprettholde høy tilgjengelighet. Denne omfattende guiden utforsker mekanikken i transaksjonsloggen, hvordan man løser en full logg i en nødssituasjon, og arkitektonisk beste praksis for å forhindre at det skjer igjen.

Forstå arkitekturen til SQL Server-transaksjonsloggen

For å effektivt feilsøke en full transaksjonslogg, må du først forstå hvordan SQL Server skriver og administrerer data.

Write-Ahead Logging (WAL)

SQL Server bruker en Write-Ahead Logging (WAL)-protokoll. Hver gang en dataendring skjer, skrives endringen først til transaksjonsloggen i minnet, og deretter tømmes den til den fysiske loggfilen på disken før de faktiske datasidene oppdateres i databasefilene (MDF/NDF). Dette garanterer ACID-samsvar (Atomicity, Consistency, Isolation, Durability), som sikrer at SQL Server kan spille av (roll forward) eller angre (roll back) transaksjoner i tilfelle en krasj.

Virtuelle loggfiler (VLF-er) og sirkulær logging

Internt er den fysiske transaksjonsloggfilen (LDF) delt inn i mindre, logiske segmenter kalt virtuelle loggfiler (VLF-er). Transaksjonsloggen opererer sirkulært. Etter hvert som loggposter skrives, fyller de én VLF og går videre til den neste.

Når loggen når slutten av den fysiske filen, forsøker den å starte på nytt fra begynnelsen. Den kan imidlertid bare overskrive en VLF hvis den VLF-en er merket som inaktiv. Hvis alle VLF-er er aktive (noe som betyr at de inneholder loggposter som SQL Server fortsatt trenger), kan ikke loggen starte på nytt. Hvis automatisk vekst er aktivert og diskplass er tilgjengelig, vokser den fysiske filen. Hvis disken er full eller automatisk vekst er begrenset, oppstår Feil 9002.

Loggavkorting (Truncation) vs. loggkrymping (Shrinking)

En vanlig misoppfatning er at avkorting av loggen reduserer den fysiske filstørrelsen.
* Loggavkorting: Prosessen med å merke aktive VLF-er som inaktive, noe som gjør plassen tilgjengelig for gjenbruk. Det reduserer ikke størrelsen på LDF-filen på disken.
* Loggkrymping: Prosessen med å fysisk redusere LDF-filstørrelsen og returnere plass til operativsystemet.

I Full Recovery-modellen skjer loggavkorting kun når en transaksjonsloggsikkerhetskopi er fullført (forutsatt at ingen andre prosesser holder loggen aktiv).

Diagnostisering av feilen “Transaksjonsloggen er full” (Feil 9002)

Når loggen er full, er ikke ditt første skritt å blindt legge til diskplass eller krympe filer. Du må identifisere hvorfor loggen ikke kan avkortes. SQL Server tilbyr en innebygd mekanisme for å fortelle deg nøyaktig hva som forhindrer logggjenbruk via sys.databases-katalogvisningen.

Kjør følgende T-SQL-kommando for å identifisere flaskehalsen:

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

Du kan også sjekke gjeldende plassbruk for transaksjonsloggene dine ved å bruke:

DBCC SQLPERF(LOGSPACE);

Vanlige log_reuse_wait_desc-tilstander

  1. LOG_BACKUP: Databasen er i Full eller Bulk-Logged gjenopprettingsmodell, og en transaksjonsloggsikkerhetskopi har ikke blitt tatt nylig. Dette er den vanligste årsaken.
  2. ACTIVE_TRANSACTION: En langvarig transaksjon (f.eks. en massiv indeksgjenoppbygging eller en glemt ikke-forpliktet transaksjon) holder loggen aktiv.
  3. REPLICATION / CDC: Transaksjonell replikering eller Change Data Capture (CDC) er aktivert, og Log Reader Agent har ennå ikke behandlet transaksjonene.
  4. AVAILABILITY_REPLICA: I en AlwaysOn Availability Group er en sekundær replika koblet fra eller synkroniserer for sakte, noe som tvinger den primære replikaen til å beholde loggposter til de er lagret på den sekundære.

Strategier for rask gjenoppretting: Løse problemet i produksjon

Avhengig av hvilken log_reuse_wait_desc som returneres, vil din beredskapsrespons variere. Her er strategiene for rask gjenoppretting for de vanligste scenarioene.

Scenario 1: Manglende eller feilende loggsikkerhetskopier (LOG_BACKUP)

Hvis ventetypen er LOG_BACKUP, er løsningen enkel: du må ta en sikkerhetskopi av transaksjonsloggen.

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

Når sikkerhetskopien er fullført, vil de inaktive VLF-ene bli avkortet, og SQL Server vil gjenoppta normal drift. Hvis sikkerhetskopidisken din er full, må du kanskje ta sikkerhetskopi til en midlertidig nettverksressurs eller en null-enhet (sterkt frarådet med mindre databasen er lett å reprodusere, da det bryter loggkjeden):

-- ADVARSEL: Dette bryter loggkjeden og kompromitterer punkt-i-tid-gjenoppretting.
-- Bruk kun hvis absolutt nødvendig og følg umiddelbart opp med en FULL sikkerhetskopi.
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';

Scenario 2: Langvarige aktive transaksjoner (ACTIVE_TRANSACTION)

Hvis en enkelt transaksjon har kjørt i timevis, forhindrer den loggavkorting i hele perioden. Identifiser først den problematiske transaksjonen:

DBCC OPENTRAN('YourDatabaseName');

Denne kommandoen returnerer den eldste aktive transaksjonen og dens Server Process ID (SPID). Du kan samle mer informasjon om hva SPID-en gjør ved å spørre dynamiske administrasjonsvisninger (DMV-er):

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

Hvis transaksjonen er en useriøs spørring eller en fastlåst prosess, må du kanskje avslutte den for å frigjøre loggen.

KILL <SPID>;

Merk: Å avbryte en massiv transaksjon vil utløse en tilbakerulling (rollback), som kan ta betydelig tid og midlertidig generere ytterligere loggaktivitet. Ikke start SQL Server-tjenesten på nytt under en tilbakerulling, ellers vil databasen gå inn i gjenopprettingsmodus ved omstart.

Scenario 3: Nødtildeling av plass (Disken er 100 % full)

Hvis LDF-filen har brukt opp hele disken, kan du ikke engang kjøre en sikkerhetskopi fordi SQL Server krever en liten mengde loggplass for å registrere selve sikkerhetskopieringshendelsen. I dette scenarioet må du legge til en sekundær loggfil på en annen disk med tilgjengelig plass.

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

Dette gir umiddelbart SQL Server litt pusterom. Når databasen er på nett, ta en transaksjonsloggsikkerhetskopi, tøm den sekundære loggfilen og fjern den:

-- 1. Ta en loggsikkerhetskopi for å avkorte loggen
BACKUP LOG [YourDatabaseName] TO DISK = '...';

-- 2. Tøm den midlertidige loggfilen
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);

-- 3. Fjern den midlertidige loggfilen
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];

Beste praksis for forebygging og administrasjon av transaksjonslogger

Reaktiv feilsøking er stressende og påvirker SLA-er. Implementering av proaktiv arkitektonisk og operasjonell beste praksis er avgjørende for stabilitet i bedriftsdatabaser.

1. Implementer en robust, automatisert sikkerhetskopieringsstrategi

Hvis en database er i Full gjenopprettingsmodell, er hyppige transaksjonsloggsikkerhetskopier obligatorisk. Avhengig av ditt mål for gjenopprettingspunkt (RPO) og transaksjonsvolum, bør loggsikkerhetskopier skje hvert 5. til 15. minutt.

Bedriftssikkerhetskopieringsløsninger som CloudSave forenkler denne prosessen betydelig. Ved å integrere direkte med SQL Server via VDI (Virtual Device Interface), lar CloudSave DBA-er konfigurere policy-drevne, høyfrekvente transaksjonsloggsikkerhetskopier. Dette sikrer at logger kontinuerlig avkortes, krypteres sikkert og lagres utenfor lokasjonen eller i uforanderlig skylagring, noe som forhindrer LOG_BACKUP-ventetilstanden uten behov for komplekse, tilpassede SQL Agent-jobber.

2. Riktig dimensjonering av transaksjonsloggen og administrasjon av VLF-er

Å stole på automatisk vekst for å administrere størrelsen på transaksjonsloggen er et farlig anti-mønster. Operasjoner for automatisk vekst er ressurskrevende og pauser transaksjonsbehandlingen mens disken nullstilles (med mindre Instant File Initialization er aktivert, noe som ikke gjelder for loggfiler).

Videre fører hyppig, liten automatisk vekst (f.eks. vekst med 10 % eller 50 MB om gangen) til VLF-fragmentering. En transaksjonslogg med tusenvis av små VLF-er vil alvorlig forringe databasens oppstartstider, sikkerhetskopieringsytelse og replikeringsforsinkelse.

  • Forhåndsdimensjoner loggen: Analyser dine største vedlikeholdsoperasjoner (som indeksgjenoppbygging) og forhåndsdimensjoner LDF-filen for å imøtekomme dem uten å måtte vokse.
  • Sett fast automatisk vekst: Endre automatisk vekst fra en prosentandel til en fast størrelse (f.eks. 1 GB eller 5 GB) for å sikre at VLF-er opprettes med en sunn størrelse.

Du kan sjekke VLF-antallet ditt ved å bruke følgende spørring (for 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'));

Hvis VLF-antallet ditt er over 500, bør du vurdere å vente til en rolig periode, krympe loggen til en minimal størrelse og manuelt vokse den tilbake til ønsket størrelse i store biter.

3. Optimaliser vedlikeholdsoperasjoner for indekser

Indeksgjenoppbygging er fullt loggførte operasjoner, selv i Bulk-Logged gjenopprettingsmodell (avhengig av indekstype). Gjenoppbygging av en 500 GB indeks vil generere minst 500 GB med transaksjonsloggposter.

For å redusere loggsvelling under vedlikehold:
* Bruk SORT_IN_TEMPDB = ON når du gjenoppbygger indekser. Dette avlaster sorteringsfasen til TempDB, noe som reduserer belastningen på databasens transaksjonslogg.
* Bytt fra indeksgjenoppbygging til indeksreorganisering der det er mulig, da reorganiseringer er mer loggeffektive og kan avbrytes uten å rulle tilbake hele operasjonen.
* Batch store DELETE– eller UPDATE-operasjoner. I stedet for å slette 10 millioner rader i én transaksjon, slett dem i biter på 50 000, forplikt (commit) og la loggsikkerhetskopier avkorte loggen mellom hver batch.

4. Overvåk høy tilgjengelighet og replikeringstopologier

I AlwaysOn Availability Groups kan ikke den primære replikaen avkorte loggen sin før loggpostene er lagret på alle synkrone og asynkrone sekundære replikaer.

Hvis en sekundær replika går offline, eller hvis nettverksbåndbredden ikke kan holde tritt med den primære replikaens transaksjonsgenereringshastighet, vil den primære replikaens sendekø vokse, og loggen vil fylles opp (AVAILABILITY_REPLICA-ventetype).

Implementer robust overvåking for ytelsestelleren SQLServer:Replica > Log Send Queue. Hvis en sekundær replika går tapt permanent, må du fjerne den fra Availability Group eller suspendere databevegelse for å tillate at den primære loggen avkortes.

Konklusjon

Å oppleve en full transaksjonslogg er en ilddåp for databaseadministratorer, men det trenger ikke å føre til langvarig nedetid. Ved å forstå mekanikken i Write-Ahead Logging og VLF-er, kan du raskt diagnostisere rotårsaken ved hjelp av sys.databases og bruke riktig strategi for rask gjenoppretting.

Langsiktig stabilitet avhenger av å bevege seg bort fra reaktive løsninger. Forhåndsdimensjonering av loggfiler, optimalisering av vedlikeholdsrutiner og bruk av sikkerhetskopieringsplattformer i bedriftsklasse som CloudSave for å håndheve strenge, automatiserte tidsplaner for loggsikkerhetskopiering, vil sikre at transaksjonsloggene dine forblir sunne, avkortede og klare til å støtte produksjonsarbeidsbelastninger med høy gjennomstrømning.

Kategorier