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.

Voor databasebeheerders (DBA’s) en DevOps-engineers die Microsoft SQL Server beheren, zijn er weinig meldingen die voor zoveel onmiddellijke paniek zorgen als Fout 9002: Het transactielogboek voor database ‘X’ is vol. Wanneer het transactielogboek vol raakt en niet meer kan groeien, wordt de database effectief alleen-lezen. Alle INSERT-, UPDATE– en DELETE-bewerkingen stoppen, transacties van applicaties mislukken en de productie komt volledig tot stilstand.

Het begrijpen van de onderliggende architectuur van het SQL Server-transactielogboek, het nauwkeurig diagnosticeren van de hoofdoorzaak en het uitvoeren van snelle herstelprocedures zijn cruciale vaardigheden voor het behoud van hoge beschikbaarheid. Deze uitgebreide gids verkent de werking van het transactielogboek, hoe u een vol logboek in een noodsituatie oplost en architecturale best practices om te voorkomen dat dit opnieuw gebeurt.

De architectuur van het SQL Server-transactielogboek begrijpen

Om een vol transactielogboek effectief te kunnen troubleshooten, moet u eerst begrijpen hoe SQL Server gegevens schrijft en beheert.

Write-Ahead Logging (WAL)

SQL Server gebruikt een Write-Ahead Logging (WAL)-protocol. Wanneer een gegevenswijziging plaatsvindt, wordt de wijziging eerst naar het transactielogboek in het geheugen geschreven en vervolgens naar het fysieke logboekbestand op de schijf geflushed voordat de daadwerkelijke gegevenspagina’s in de databasebestanden (MDF/NDF) worden bijgewerkt. Dit garandeert ACID-compliance (Atomicity, Consistency, Isolation, Durability), wat ervoor zorgt dat SQL Server bij een crash transacties kan herhalen (roll forward) of ongedaan kan maken (roll back).

Virtual Log Files (VLF’s) en circulair loggen

Intern is het fysieke transactielogboekbestand (LDF) verdeeld in kleinere, logische segmenten genaamd Virtual Log Files (VLF’s). Het transactielogboek werkt circulair. Terwijl logboekrecords worden geschreven, vullen ze één VLF en gaan ze door naar de volgende.

Wanneer het logboek het einde van het fysieke bestand bereikt, probeert het terug te keren naar het begin. Het kan echter alleen een VLF overschrijven als die VLF is gemarkeerd als inactief. Als alle VLF’s actief zijn (wat betekent dat ze logboekrecords bevatten die nog steeds nodig zijn voor SQL Server), kan het logboek niet ronddraaien. Als automatisch groeien (auto-growth) is ingeschakeld en er schijfruimte beschikbaar is, groeit het fysieke bestand. Als de schijf vol is of als automatisch groeien is beperkt, krijgt u Fout 9002.

Logboektruncatie versus logboekverkleining (Shrinking)

Een veelvoorkomend misverstand is dat het trunkeren van het logboek de fysieke bestandsgrootte verkleint.
* Logboektruncatie: Het proces waarbij actieve VLF’s als inactief worden gemarkeerd, waardoor de ruimte beschikbaar komt voor hergebruik. Dit verkleint niet de grootte van het LDF-bestand op de schijf.
* Logboekverkleining (Shrinking): Het proces waarbij de LDF-bestandsgrootte fysiek wordt verkleind en ruimte wordt teruggegeven aan het besturingssysteem.

In het Full Recovery-model vindt logboektruncatie alleen plaats wanneer een transactielogboekback-up succesvol is voltooid (ervan uitgaande dat geen andere processen het logboek actief houden).

De fout “Transactielogboek vol” diagnosticeren (Fout 9002)

Wanneer het logboek vol is, is uw eerste stap niet om blindelings schijfruimte toe te voegen of bestanden te verkleinen. U moet identificeren waarom het logboek niet kan trunkeren. SQL Server biedt een ingebouwd mechanisme om u precies te vertellen wat logboekhergebruik verhindert via de sys.databases catalogusweergave.

Voer het volgende T-SQL-commando uit om de knelpunt te identificeren:

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

U kunt ook het huidige ruimtegebruik van uw transactielogboeken controleren met:

DBCC SQLPERF(LOGSPACE);

Veelvoorkomende log_reuse_wait_desc statussen

  1. LOG_BACKUP: De database bevindt zich in het Full of Bulk-Logged herstelmodel en er is onlangs geen transactielogboekback-up gemaakt. Dit is de meest voorkomende oorzaak.
  2. ACTIVE_TRANSACTION: Een langlopende transactie (bijv. een enorme index-rebuild of een vergeten niet-gecommitteerde transactie) houdt het logboek actief.
  3. REPLICATION / CDC: Transactionele replicatie of Change Data Capture (CDC) is ingeschakeld en de Log Reader Agent heeft de transacties nog niet verwerkt.
  4. AVAILABILITY_REPLICA: In een AlwaysOn Availability Group is een secundaire replica losgekoppeld of synchroniseert deze te langzaam, waardoor de primaire replica logboekrecords moet vasthouden totdat ze op de secundaire replica zijn vastgelegd.

Strategieën voor snel herstel: Het probleem oplossen in productie

Afhankelijk van de geretourneerde log_reuse_wait_desc zal uw noodrespons variëren. Hier zijn de strategieën voor snel herstel voor de meest voorkomende scenario’s.

Scenario 1: Ontbrekende of mislukte logboekback-ups (LOG_BACKUP)

Als het wachttype LOG_BACKUP is, is de oplossing eenvoudig: u moet een back-up van het transactielogboek maken.

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

Zodra de back-up is voltooid, worden de inactieve VLF’s getrunkeerd en zal SQL Server de normale operaties hervatten. Als uw back-upschijf vol is, moet u mogelijk een back-up maken naar een tijdelijke netwerkshare of een null-apparaat (sterk afgeraden tenzij de database eenvoudig reproduceerbaar is, omdat dit de logboekketen verbreekt):

-- WAARSCHUWING: Dit verbreekt de logboekketen en brengt point-in-time herstel in gevaar.
-- Gebruik dit alleen als het absoluut noodzakelijk is en volg direct op met een VOLLEDIGE back-up.
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';

Scenario 2: Langlopende actieve transacties (ACTIVE_TRANSACTION)

Als een enkele transactie al uren draait, voorkomt dit logboektruncatie voor de gehele duur. Identificeer eerst de problematische transactie:

DBCC OPENTRAN('YourDatabaseName');

Dit commando retourneert de oudste actieve transactie en het Server Process ID (SPID). U kunt meer details verzamelen over wat de SPID doet door dynamische beheerweergaven (DMV’s) te bevragen:

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

Als de transactie een rogue query of een vastgelopen proces is, moet u deze mogelijk beëindigen om het logboek vrij te maken.

KILL <SPID>;

Let op: Het beëindigen van een enorme transactie zal een rollback activeren, wat aanzienlijke tijd in beslag kan nemen en tijdelijk extra logboekactiviteit genereert. Start de SQL Server-service niet opnieuw op tijdens een rollback, anders zal de database bij het opnieuw opstarten in de herstelmodus gaan.

Scenario 3: Noodtoewijzing van ruimte (Schijf is 100% vol)

Als het LDF-bestand de volledige schijf heeft verbruikt, kunt u niet eens een back-up uitvoeren omdat SQL Server een kleine hoeveelheid logboekruimte nodig heeft om de back-upgebeurtenis zelf vast te leggen. In dit scenario moet u een secundair logboekbestand toevoegen op een andere schijf met beschikbare ruimte.

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

Dit geeft SQL Server onmiddellijk ademruimte. Zodra de database online is, maakt u een transactielogboekback-up, maakt u het secundaire logboekbestand leeg en verwijdert u het:

-- 1. Maak een logboekback-up om het logboek te trunkeren
BACKUP LOG [YourDatabaseName] TO DISK = '...';

-- 2. Maak het tijdelijke logboekbestand leeg
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);

-- 3. Verwijder het tijdelijke logboekbestand
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];

Best practices voor preventie en beheer van het transactielogboek

Reactieve probleemoplossing is stressvol en beïnvloedt SLA’s. Het implementeren van proactieve architecturale en operationele best practices is essentieel voor de stabiliteit van bedrijfsdatabases.

1. Implementeer een robuuste, geautomatiseerde back-upstrategie

Als een database in het Full recovery-model staat, zijn frequente transactielogboekback-ups verplicht. Afhankelijk van uw Recovery Point Objective (RPO) en transactievolume, zouden logboekback-ups elke 5 tot 15 minuten moeten plaatsvinden.

Enterprise back-upoplossingen zoals CloudSave vereenvoudigen dit proces aanzienlijk. Door direct te integreren met SQL Server via VDI (Virtual Device Interface), stelt CloudSave DBA’s in staat om beleidsgestuurde, hoogfrequente transactielogboekback-ups te configureren. Dit zorgt ervoor dat logboeken continu worden getrunkeerd, veilig worden versleuteld en off-site of in onveranderlijke cloudopslag worden opgeslagen, waardoor de LOG_BACKUP wachtstatus wordt voorkomen zonder complexe aangepaste SQL Agent-taken.

2. Het transactielogboek op de juiste grootte brengen en VLF’s beheren

Vertrouwen op automatisch groeien om de grootte van uw transactielogboek te beheren is een gevaarlijk anti-patroon. Operaties voor automatisch groeien zijn kostbaar en pauzeren de transactieverwerking terwijl de schijf met nullen wordt geïnitialiseerd (tenzij Instant File Initialization is ingeschakeld, wat niet van toepassing is op logboekbestanden).

Bovendien leiden frequente, kleine automatische groei-acties (bijv. telkens met 10% of 50MB groeien) tot VLF-fragmentatie. Een transactielogboek met duizenden kleine VLF’s zal de opstarttijden van de database, back-upprestaties en replicatielatentie ernstig verslechteren.

  • Bepaal vooraf de grootte van het logboek: Analyseer uw grootste onderhoudsoperaties (zoals index-rebuilds) en bepaal vooraf de grootte van het LDF-bestand om ze op te vangen zonder te groeien.
  • Stel vaste automatische groei in: Wijzig automatisch groeien van een percentage naar een vaste grootte (bijv. 1GB of 5GB) om ervoor te zorgen dat VLF’s op een gezonde grootte worden gemaakt.

U kunt uw VLF-aantal controleren met de volgende query (voor 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'));

Als uw VLF-aantal boven de 500 ligt, overweeg dan om te wachten op een rustige periode, het logboek te verkleinen tot een minimale grootte en het handmatig in grote stappen terug te laten groeien naar de vereiste grootte.

3. Optimaliseer indexonderhoudsoperaties

Index-rebuilds zijn volledig gelogde operaties, zelfs in het Bulk-Logged herstelmodel (afhankelijk van het indextype). Het opnieuw opbouwen van een index van 500GB genereert ten minste 500GB aan transactielogboekrecords.

Om logboekopzwelling tijdens onderhoud te beperken:
* Gebruik SORT_IN_TEMPDB = ON bij het opnieuw opbouwen van indexen. Dit verplaatst de sorteerfase naar TempDB, waardoor de belasting op het transactielogboek van de gebruikersdatabase wordt verminderd.
* Schakel waar mogelijk over van index-rebuilds naar index-reorganisaties, aangezien reorganisaties logboek-efficiënter zijn en kunnen worden onderbroken zonder de gehele operatie terug te draaien.
* Batch grote DELETE– of UPDATE-operaties. In plaats van 10 miljoen rijen in één transactie te verwijderen, verwijdert u ze in blokken van 50.000, waarbij u commit en logboekback-ups toestaat om het logboek tussen de batches door te trunkeren.

4. Monitor hoge beschikbaarheid en replicatietopologieën

In AlwaysOn Availability Groups kan de primaire replica zijn logboek niet trunkeren totdat de logboekrecords zijn vastgelegd op alle synchrone en asynchrone secundaire replica’s.

Als een secundaire replica offline gaat, of als de netwerkbandbreedte de transactiegeneratiesnelheid van de primaire niet kan bijhouden, zal de verzendwachtrij van de primaire groeien en zal het logboek vol raken (AVAILABILITY_REPLICA wachttype).

Implementeer robuuste monitoring voor de SQLServer:Replica > Log Send Queue prestatiemeter. Als een secundaire replica permanent verloren is gegaan, moet u deze uit de Availability Group verwijderen of de gegevensverplaatsing opschorten om het primaire logboek te laten trunkeren.

Conclusie

Het tegenkomen van een vol transactielogboek is een vuurdoop voor databasebeheerders, maar het hoeft niet te resulteren in langdurige downtime. Door de werking van Write-Ahead Logging en VLF’s te begrijpen, kunt u snel de hoofdoorzaak diagnosticeren met sys.databases en de juiste strategie voor snel herstel toepassen.

Stabiliteit op lange termijn berust op het afstappen van reactieve oplossingen. Het vooraf bepalen van de grootte van uw logboekbestanden, het optimaliseren van onderhoudsroutines en het gebruik van enterprise-grade back-upplatforms zoals CloudSave om strikte, geautomatiseerde logboekback-upschema’s af te dwingen, zorgt ervoor dat uw transactielogboeken gezond, getrunkeerd en klaar blijven om productieworkloads met een hoge doorvoer te ondersteunen.

Categorieën