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, der administrerer Microsoft SQL Server, er der få advarsler, der skaber så øjeblikkelig angst som Fejl 9002: Transaktionsloggen for databasen ‘X’ er fuld. Når transaktionsloggen bliver fuld og ikke kan vokse, bliver databasen reelt skrivebeskyttet. Alle INSERT-, UPDATE– og DELETE-handlinger stopper, applikationstransaktioner fejler, og produktionen går i stå.

At forstå den underliggende arkitektur i SQL Server-transaktionsloggen, præcist at diagnosticere årsagen og udføre hurtige genopretningsprocedurer er kritiske færdigheder for at opretholde høj tilgængelighed. Denne omfattende guide udforsker transaktionsloggens mekanik, hvordan man løser en fuld log i en nødsituation, og arkitektoniske best practices for at forhindre, at det sker igen.

Forståelse af SQL Server-transaktionslogarkitektur

For effektivt at fejlfinde en fuld transaktionslog skal du først forstå, hvordan SQL Server skriver og administrerer data.

Write-Ahead Logging (WAL)

SQL Server bruger en Write-Ahead Logging (WAL)-protokol. Hver gang en datamodificering finder sted, skrives ændringen først til transaktionsloggen i hukommelsen og skylles derefter til den fysiske logfil på disken, før de faktiske datasider opdateres i databasefilerne (MDF/NDF). Dette garanterer ACID-overholdelse (Atomicity, Consistency, Isolation, Durability), hvilket sikrer, at SQL Server i tilfælde af et nedbrud kan afspille (roll forward) eller fortryde (roll back) transaktioner.

Virtuelle logfiler (VLF’er) og cirkulær logning

Internt er den fysiske transaktionslogfil (LDF) opdelt i mindre, logiske segmenter kaldet Virtual Log Files (VLF’er). Transaktionsloggen fungerer cirkulært. Efterhånden som logposter skrives, udfylder de én VLF og går videre til den næste.

Når loggen når slutningen af den fysiske fil, forsøger den at starte forfra. Den kan dog kun overskrive en VLF, hvis denne VLF er markeret som inaktiv. Hvis alle VLF’er er aktive (hvilket betyder, at de indeholder logposter, som SQL Server stadig har brug for), kan loggen ikke starte forfra. Hvis auto-vækst er aktiveret, og der er diskplads til rådighed, vokser den fysiske fil. Hvis disken er fuld, eller auto-vækst er begrænset, støder du på Fejl 9002.

Log-trunkering vs. Log-formindskelse (Shrinking)

En almindelig misforståelse er, at trunkering af loggen reducerer den fysiske filstørrelse.
* Log-trunkering: Processen med at markere aktive VLF’er som inaktive, hvilket gør pladsen tilgængelig til genbrug. Det reducerer ikke størrelsen på LDF-filen på disken.
* Log-formindskelse (Shrinking): Processen med fysisk at reducere LDF-filstørrelsen og returnere plads til operativsystemet.

I Full Recovery-modellen sker log-trunkering kun, når en transaktionslog-backup er gennemført (forudsat at ingen andre processer holder loggen aktiv).

Diagnosticering af fejlen “Transaktionslog fuld” (Fejl 9002)

Når loggen er fuld, er dit første skridt ikke blindt at tilføje diskplads eller formindske filer. Du skal identificere, hvorfor loggen ikke kan trunkeres. SQL Server leverer en indbygget mekanisme til at fortælle dig præcis, hvad der forhindrer log-genbrug via sys.databases-katalogvisningen.

Kør følgende T-SQL-kommando for at identificere 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å tjekke det aktuelle pladsforbrug for dine transaktionslogs ved at bruge:

DBCC SQLPERF(LOGSPACE);

Almindelige log_reuse_wait_desc-tilstande

  1. LOG_BACKUP: Databasen er i Full eller Bulk-Logged recovery-modellen, og der er ikke taget en transaktionslog-backup for nylig. Dette er den mest almindelige årsag.
  2. ACTIVE_TRANSACTION: En langvarig transaktion (f.eks. en massiv indeksgenopbygning eller en glemt ikke-committet transaktion) holder loggen aktiv.
  3. REPLICATION / CDC: Transactional Replication eller Change Data Capture (CDC) er aktiveret, og Log Reader Agent har endnu ikke behandlet transaktionerne.
  4. AVAILABILITY_REPLICA: I en AlwaysOn Availability Group er en sekundær replika afbrudt eller synkroniserer for langsomt, hvilket tvinger den primære replika til at beholde logposter, indtil de er bekræftet på den sekundære.

Strategier for hurtig genopretning: Løsning af problemet i produktion

Afhængigt af den returnerede log_reuse_wait_desc vil din nødrespons variere. Her er strategierne for hurtig genopretning til de mest almindelige scenarier.

Scenarie 1: Manglende eller fejlslagne log-backups (LOG_BACKUP)

Hvis ventetypen er LOG_BACKUP, er løsningen ligetil: Du skal tage en backup af transaktionsloggen.

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

Når backuppen er færdig, vil de inaktive VLF’er blive trunkeret, og SQL Server vil genoptage normal drift. Hvis dit backup-drev er fuldt, skal du muligvis tage backup til et midlertidigt netværksdrev eller en null-enhed (frarådes kraftigt, medmindre databasen let kan genskabes, da det bryder logkæden):

-- ADVARSEL: Dette bryder logkæden og kompromitterer point-in-time recovery.
-- Brug kun hvis absolut nødvendigt og følg straks op med en FULD backup.
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';

Scenarie 2: Langvarige aktive transaktioner (ACTIVE_TRANSACTION)

Hvis en enkelt transaktion har kørt i timevis, forhindrer den log-trunkering i hele perioden. Identificer først den problematiske transaktion:

DBCC OPENTRAN('YourDatabaseName');

Denne kommando returnerer den ældste aktive transaktion og dens Server Process ID (SPID). Du kan indsamle flere detaljer om, hvad SPID’et laver, ved at forespørge dynamiske administrationsvisninger (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 transaktionen er en rogue-forespørgsel eller en fastlåst proces, skal du muligvis afslutte den for at frigøre loggen.

KILL <SPID>;

Bemærk: Afslutning af en massiv transaktion vil udløse en rollback, hvilket kan tage betydelig tid og midlertidigt generere yderligere logaktivitet. Genstart ikke SQL Server-tjenesten under en rollback, ellers vil databasen gå i recovery-tilstand ved genstart.

Scenarie 3: Nødtildeling af plads (Disken er 100% fuld)

Hvis LDF-filen har opbrugt hele drevet, kan du ikke engang køre en backup, fordi SQL Server kræver en lille smule logplads for at registrere selve backup-hændelsen. I dette scenarie skal du tilføje en sekundær logfil på et andet drev med ledig plads.

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

Dette giver straks SQL Server lidt luft. Når databasen er online, skal du tage en transaktionslog-backup, tømme den sekundære logfil og fjerne den:

-- 1. Tag en log-backup for at trunkere loggen
BACKUP LOG [YourDatabaseName] TO DISK = '...';

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

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

Best practices for forebyggelse og styring af transaktionsloggen

Reaktiv fejlfinding er stressende og påvirker SLA’er. Implementering af proaktive arkitektoniske og operationelle best practices er afgørende for stabiliteten af virksomhedsdatabaser.

1. Implementer en robust, automatiseret backup-strategi

Hvis en database er i Full recovery-modellen, er hyppige transaktionslog-backups obligatoriske. Afhængigt af dit Recovery Point Objective (RPO) og transaktionsvolumen bør log-backups ske hvert 5. til 15. minut.

Enterprise-backup-løsninger som CloudSave forenkler denne proces betydeligt. Ved at integrere direkte med SQL Server via VDI (Virtual Device Interface) giver CloudSave DBA’er mulighed for at konfigurere politikstyrede, højfrekvente transaktionslog-backups. Dette sikrer, at logs løbende trunkeres, krypteres sikkert og gemmes off-site eller i uforanderlig cloud-lagring, hvilket forhindrer LOG_BACKUP-ventetilstanden uden behov for komplekse, tilpassede SQL Agent-jobs.

2. Dimensioner transaktionsloggen korrekt og administrer VLF’er

At stole på auto-vækst til at styre din transaktionslogstørrelse er et farligt anti-mønster. Auto-vækst-operationer er dyre og pauser transaktionsbehandlingen, mens disken nul-initialiseres (medmindre Instant File Initialization er aktiveret, hvilket ikke gælder for logfiler).

Desuden fører hyppige, små auto-vækster (f.eks. vækst med 10% eller 50MB ad gangen) til VLF-fragmentering. En transaktionslog med tusindvis af små VLF’er vil alvorligt forringe databasens opstartstider, backup-ydeevne og replikeringsforsinkelse.

  • For-dimensioner loggen: Analyser dine største vedligeholdelsesoperationer (som indeksgenopbygninger) og for-dimensioner LDF-filen, så den kan rumme dem uden at vokse.
  • Indstil fast auto-vækst: Skift auto-vækst fra en procentdel til en fast størrelse (f.eks. 1GB eller 5GB) for at sikre, at VLF’er oprettes i en sund størrelse.

Du kan tjekke dit VLF-antal ved hjælp af følgende forespørgsel (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 dit VLF-antal er over 500, bør du overveje at vente på en rolig periode, formindske loggen til en minimal størrelse og manuelt vokse den tilbage til den ønskede størrelse i store bidder.

3. Optimer indeksvedligeholdelsesoperationer

Indeksgenopbygninger er fuldt loggede operationer, selv i Bulk-Logged recovery-modellen (afhængigt af indekstypen). Genopbygning af et 500GB indeks vil generere mindst 500GB transaktionslogposter.

For at mindske log-oppustning under vedligeholdelse:
* Brug SORT_IN_TEMPDB = ON ved genopbygning af indekser. Dette flytter sorteringsfasen til TempDB, hvilket reducerer belastningen på databasens transaktionslog.
* Skift fra indeks-genopbygninger til indeks-reorganiseringer, hvor det er muligt, da reorganiseringer er mere log-effektive og kan afbrydes uden at rulle hele operationen tilbage.
* Batch store DELETE– eller UPDATE-operationer. I stedet for at slette 10 millioner rækker i én transaktion, slet dem i bidder af 50.000, commit og tillad log-backups at trunkere loggen mellem batcherne.

4. Overvåg høj tilgængelighed og replikeringstopologier

I AlwaysOn Availability Groups kan den primære replika ikke trunkere sin log, før logposterne er bekræftet på alle synkrone og asynkrone sekundære replikaer.

Hvis en sekundær replika går offline, eller hvis netværksbåndbredden ikke kan følge med den primære replikas transaktionsgenereringshastighed, vil den primære replikas sendekø vokse, og loggen vil blive fuld (AVAILABILITY_REPLICA-ventetype).

Implementer robust overvågning af SQLServer:Replica > Log Send Queue-performance-tælleren. Hvis en sekundær replika er permanent tabt, skal du fjerne den fra Availability Group eller suspendere databevægelse for at tillade den primære log at trunkere.

Konklusion

At støde på en fuld transaktionslog er en dåbsgave for databaseadministratorer, men det behøver ikke at resultere i langvarig nedetid. Ved at forstå mekanikken i Write-Ahead Logging og VLF’er kan du hurtigt diagnosticere årsagen ved hjælp af sys.databases og anvende den korrekte strategi for hurtig genopretning.

Langsigtet stabilitet afhænger af at bevæge sig væk fra reaktive rettelser. For-dimensionering af dine logfiler, optimering af vedligeholdelsesrutiner og brug af enterprise-backup-platforme som CloudSave til at håndhæve strenge, automatiserede log-backup-planer vil sikre, at dine transaktionslogs forbliver sunde, trunkerede og klar til at understøtte produktionsarbejdsbelastninger med høj gennemstrømning.

Kategorier