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.

Tietokantojen ylläpitäjille (DBA) ja DevOps-insinööreille, jotka hallinnoivat Microsoft SQL Serveriä, harva hälytys aiheuttaa yhtä välitöntä ahdistusta kuin virhe 9002: The transaction log for database ’X’ is full (Tietokannan ’X’ tapahtumaloki on täynnä). Kun tapahtumaloki täyttyy eikä se voi kasvaa, tietokannasta tulee käytännössä vain luku -tilaan lukittu. Kaikki INSERT-, UPDATE– ja DELETE-toiminnot pysähtyvät, sovellusten transaktiot epäonnistuvat ja tuotantoympäristö pysähtyy.

SQL Serverin tapahtumalokin arkkitehtuurin ymmärtäminen, perussyyn tarkka diagnosointi ja nopeiden palautustoimenpiteiden suorittaminen ovat kriittisiä taitoja korkean käytettävyyden ylläpitämiseksi. Tämä kattava opas käsittelee tapahtumalokin toimintaperiaatteita, täyden lokin ratkaisemista hätätilanteessa sekä arkkitehtuurin parhaita käytäntöjä, joilla estetään ongelman toistuminen.

SQL Serverin tapahtumalokin arkkitehtuurin ymmärtäminen

Jotta voit tehokkaasti vianmäärittää täyden tapahtumalokin, sinun on ensin ymmärrettävä, miten SQL Server kirjoittaa ja hallitsee tietoja.

Write-Ahead Logging (WAL)

SQL Server käyttää Write-Ahead Logging (WAL) -protokollaa. Aina kun tietoja muokataan, muutos kirjoitetaan ensin muistissa olevaan tapahtumalokiin ja huuhdellaan sitten levyllä olevaan fyysiseen lokitiedostoon ennen kuin varsinaiset tietosivut päivitetään tietokantatiedostoissa (MDF/NDF). Tämä takaa ACID-yhteensopivuuden (Atomicity, Consistency, Isolation, Durability), mikä varmistaa, että kaatumistilanteessa SQL Server voi toistaa (roll forward) tai kumota (roll back) transaktiot.

Virtuaaliset lokitiedostot (VLF) ja kiertävä lokitus

Sisäisesti fyysinen tapahtumalokitiedosto (LDF) on jaettu pienempiin, loogisiin segmentteihin, joita kutsutaan virtuaalisiksi lokitiedostoiksi (VLF). Tapahtumaloki toimii kiertävästi. Kun lokitietueita kirjoitetaan, ne täyttävät yhden VLF:n ja siirtyvät seuraavaan.

Kun loki saavuttaa fyysisen tiedoston lopun, se yrittää kiertää takaisin alkuun. Se voi kuitenkin ylikirjoittaa VLF:n vain, jos se on merkitty inaktiiviseksi. Jos kaikki VLF:t ovat aktiivisia (eli ne sisältävät lokitietueita, joita SQL Server tarvitsee edelleen), loki ei voi kiertää. Jos automaattinen kasvu (auto-growth) on käytössä ja levytilaa on vapaana, fyysinen tiedosto kasvaa. Jos levy on täynnä tai automaattinen kasvu on rajoitettu, kohtaat virheen 9002.

Lokin typistäminen (Truncation) vs. lokin kutistaminen (Shrinking)

Yleinen väärinkäsitys on, että lokin typistäminen pienentää fyysisen tiedoston kokoa.
* Lokin typistäminen: Prosessi, jossa aktiiviset VLF:t merkitään inaktiivisiksi, jolloin tila vapautuu uudelleenkäyttöön. Se ei pienennä LDF-tiedoston kokoa levyllä.
* Lokin kutistaminen: Prosessi, jossa LDF-tiedoston kokoa pienennetään fyysisesti ja tila palautetaan käyttöjärjestelmälle.

Full Recovery -mallissa lokin typistäminen tapahtuu vain, kun tapahtumalokin varmuuskopiointi on suoritettu onnistuneesti (olettaen, ettei mikään muu prosessi pidä lokia aktiivisena).

”Transaction Log Full” -virheen (virhe 9002) diagnosointi

Kun loki on täynnä, ensimmäinen askel ei ole lisätä levytilaa tai kutistaa tiedostoja sokkona. Sinun on tunnistettava, miksi lokia ei voida typistää. SQL Server tarjoaa sisäänrakennetun mekanismin, jolla näet tarkalleen, mikä estää lokin uudelleenkäytön sys.databases-kataloginäkymän kautta.

Suorita seuraava T-SQL-komento tunnistaaksesi pullonkaulan:

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

Voit myös tarkistaa tapahtumalokien nykyisen tilankäytön komennolla:

DBCC SQLPERF(LOGSPACE);

Yleiset log_reuse_wait_desc-tilat

  1. LOG_BACKUP: Tietokanta on Full- tai Bulk-Logged-palautusmallissa, eikä tapahtumalokista ole otettu varmuuskopiota viime aikoina. Tämä on yleisin syy.
  2. ACTIVE_TRANSACTION: Pitkään käynnissä oleva transaktio (esim. massiivinen indeksin uudelleenrakennus tai unohtunut vahvistamaton transaktio) pitää lokin aktiivisena.
  3. REPLICATION / CDC: Transactional Replication tai Change Data Capture (CDC) on käytössä, eikä Log Reader Agent ole vielä käsitellyt transaktioita.
  4. AVAILABILITY_REPLICA: AlwaysOn Availability Group -ryhmässä toissijainen replika on katkaissut yhteyden tai synkronoi liian hitaasti, mikä pakottaa ensisijaisen replikan säilyttämään lokitietueet, kunnes ne on vahvistettu toissijaisessa replikassa.

Nopeat palautusstrategiat: Ongelman ratkaiseminen tuotannossa

Hätätilanteen toimintatapa riippuu palautetusta log_reuse_wait_desc-arvosta. Tässä on nopeita palautusstrategioita yleisimpiin skenaarioihin.

Skenaario 1: Puuttuvat tai epäonnistuneet lokivarmuuskopiot (LOG_BACKUP)

Jos odotustyyppi on LOG_BACKUP, ratkaisu on suoraviivainen: sinun on otettava tapahtumalokista varmuuskopio.

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

Kun varmuuskopiointi on valmis, inaktiiviset VLF:t typistetään ja SQL Server jatkaa normaalia toimintaa. Jos varmuuskopiolevysi on täynnä, saatat joutua varmuuskopioimaan väliaikaiselle verkkolevylle tai null-laitteelle (erittäin epäsuositeltavaa, ellei tietokantaa voi helposti palauttaa, sillä se katkaisee lokiketjun):

-- VAROITUS: Tämä katkaisee lokiketjun ja vaarantaa ajanhetkeen palauttamisen.
-- Käytä vain, jos se on ehdottoman välttämätöntä, ja suorita heti perään FULL-varmuuskopio.
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';

Skenaario 2: Pitkään käynnissä olevat aktiiviset transaktiot (ACTIVE_TRANSACTION)

Jos yksittäinen transaktio on ollut käynnissä tuntikausia, se estää lokin typistämisen koko ajaksi. Tunnista ensin ongelmallinen transaktio:

DBCC OPENTRAN('YourDatabaseName');

Tämä komento palauttaa vanhimman aktiivisen transaktion ja sen prosessi-ID:n (SPID). Voit kerätä lisätietoja siitä, mitä SPID tekee, kyselemällä dynaamisia hallintanäkymiä (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>;

Jos transaktio on virheellinen kysely tai jumiutunut prosessi, saatat joutua lopettamaan sen lokin vapauttamiseksi.

KILL <SPID>;

Huomautus: Massiivisen transaktion tappaminen käynnistää kumoamisen (rollback), mikä voi viedä huomattavan paljon aikaa ja luo väliaikaisesti lisää lokitoimintaa. Älä käynnistä SQL Server -palvelua uudelleen kumoamisen aikana, muuten tietokanta siirtyy palautustilaan uudelleenkäynnistyksen yhteydessä.

Skenaario 3: Hätätilan varaaminen (levy on 100 % täynnä)

Jos LDF-tiedosto on kuluttanut koko levyn, et voi edes suorittaa varmuuskopiota, koska SQL Server vaatii pienen määrän lokitilaa itse varmuuskopiotapahtuman kirjaamiseen. Tässä skenaariossa sinun on lisättävä toissijainen lokitiedosto toiselle levylle, jossa on vapaata tilaa.

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

Tämä antaa SQL Serverille välittömästi hengitystilaa. Kun tietokanta on online-tilassa, ota tapahtumalokin varmuuskopio, tyhjennä toissijainen lokitiedosto ja poista se:

-- 1. Ota lokivarmuuskopio lokin typistämiseksi
BACKUP LOG [YourDatabaseName] TO DISK = '...';

-- 2. Tyhjennä väliaikainen lokitiedosto
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);

-- 3. Poista väliaikainen lokitiedosto
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];

Parhaat käytännöt tapahtumalokin ehkäisyyn ja hallintaan

Reaktiivinen vianmääritys on stressaavaa ja vaikuttaa palvelutasosopimuksiin (SLA). Ennakoivien arkkitehtuurin ja toiminnan parhaiden käytäntöjen toteuttaminen on välttämätöntä yritystietokantojen vakaudelle.

1. Toteuta vankka, automatisoitu varmuuskopiointistrategia

Jos tietokanta on Full-palautusmallissa, tiheät tapahtumalokien varmuuskopiot ovat pakollisia. Riippuen palautustavoitteestasi (RPO) ja transaktiomäärästä, lokivarmuuskopioiden tulisi tapahtua 5–15 minuutin välein.

Yritystason varmuuskopiointiratkaisut, kuten CloudSave, yksinkertaistavat tätä prosessia merkittävästi. Integroimalla suoraan SQL Serveriin VDI:n (Virtual Device Interface) kautta, CloudSave mahdollistaa DBA-ylläpitäjille käytäntöperusteisten, korkean tiheyden tapahtumalokien varmuuskopioiden määrittämisen. Tämä varmistaa, että lokit typistetään jatkuvasti, salataan turvallisesti ja tallennetaan muuttumattomaan pilvitallennustilaan, mikä estää LOG_BACKUP-odotustilan ilman monimutkaisia mukautettuja SQL Agent -töitä.

2. Mitoita tapahtumaloki oikein ja hallitse VLF-tiedostoja

Automaattiseen kasvuun luottaminen tapahtumalokin koon hallinnassa on vaarallinen virhe. Automaattiset kasvutoiminnot ovat raskaita ja keskeyttävät transaktioiden käsittelyn, kun levy alustetaan nollilla (ellei Instant File Initialization ole käytössä, mikä ei koske lokitiedostoja).

Lisäksi toistuvat, pienet automaattiset kasvuoperaatiot (esim. 10 % tai 50 MB kerrallaan) johtavat VLF-fragmentaatioon. Tapahtumaloki, jossa on tuhansia pieniä VLF-tiedostoja, heikentää merkittävästi tietokannan käynnistysaikoja, varmuuskopioinnin suorituskykyä ja replikoinnin viivettä.

  • Esikokoa loki: Analysoi suurimmat ylläpitotoiminnot (kuten indeksien uudelleenrakennukset) ja esikokoa LDF-tiedosto niin, että ne mahtuvat siihen ilman kasvua.
  • Aseta kiinteä automaattinen kasvu: Muuta automaattinen kasvu prosenttiosuudesta kiinteäksi kooksi (esim. 1 GB tai 5 GB), jotta VLF-tiedostot luodaan järkevän kokoisina.

Voit tarkistaa VLF-määrän seuraavalla kyselyllä (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'));

Jos VLF-määräsi on yli 500, harkitse odottamista rauhalliseen ajankohtaan, lokin kutistamista minimikokoon ja sen manuaalista kasvattamista takaisin vaadittuun kokoon suurina paloina.

3. Optimoi indeksien ylläpitotoiminnot

Indeksien uudelleenrakennukset ovat täysin lokitettuja toimintoja, jopa Bulk-Logged-palautusmallissa (indeksityypistä riippuen). 500 GB:n indeksin uudelleenrakentaminen tuottaa vähintään 500 GB tapahtumalokitietueita.

Lokin paisumisen hillitsemiseksi ylläpidon aikana:
* Käytä SORT_IN_TEMPDB = ON -asetusta indeksejä uudelleenrakennettaessa. Tämä siirtää lajitteluvaiheen TempDB:hen, mikä vähentää käyttäjätietokannan tapahtumalokin kuormitusta.
* Vaihda indeksien uudelleenrakennuksesta (rebuild) indeksien uudelleenjärjestelyyn (reorganize) mahdollisuuksien mukaan, sillä uudelleenjärjestelyt ovat lokitehokkaampia ja ne voidaan keskeyttää ilman koko operaation kumoamista.
* Eräajoita suuret DELETE– tai UPDATE-toiminnot. Sen sijaan, että poistaisit 10 miljoonaa riviä yhdessä transaktiossa, poista ne 50 000 rivin erissä, vahvistaen transaktiot ja antaen lokivarmuuskopioiden typistää lokin erien välissä.

4. Valvo korkeaa käytettävyyttä ja replikointitopologioita

AlwaysOn Availability Groups -ryhmissä ensisijainen replika ei voi typistää lokiaan ennen kuin lokitietueet on vahvistettu kaikissa synkronisissa ja asynkronisissa toissijaisissa replikoissa.

Jos toissijainen replika menee offline-tilaan tai jos verkon kaistanleveys ei pysy ensisijaisen replikan transaktioiden luontinopeuden perässä, ensisijaisen replikan lähetysjono kasvaa ja loki täyttyy (AVAILABILITY_REPLICA-odotustyyppi).

Toteuta vankka valvonta SQLServer:Replica > Log Send Queue -suorituskykymittarille. Jos toissijainen replika katoaa pysyvästi, sinun on poistettava se Availability Group -ryhmästä tai keskeytettävä tiedonsiirto, jotta ensisijaisen replikan loki voi typistyä.

Johtopäätös

Täyteen tapahtumalokiin törmääminen on tietokantojen ylläpitäjien ”tulikoe”, mutta sen ei tarvitse johtaa pitkiin käyttökatkoihin. Ymmärtämällä Write-Ahead Loggingin ja VLF-tiedostojen toimintaperiaatteet voit diagnosoida perussyyn nopeasti sys.databases-näkymän avulla ja soveltaa oikeaa nopeaa palautusstrategiaa.

Pitkän aikavälin vakaus perustuu reaktiivisista korjauksista luopumiseen. Lokitiedostojen esikokoaminen, ylläpitorutiinien optimointi ja yritystason varmuuskopiointialustojen, kuten CloudSaven, hyödyntäminen tiukkojen, automatisoitujen lokivarmuuskopioaikataulujen noudattamiseksi varmistavat, että tapahtumalokisi pysyvät terveinä, typistettyinä ja valmiina tukemaan korkean suorituskyvyn tuotantokuormia.

Kategoriat