Fyrir gagnagrunnsstjóra (DBA) og DevOps-verkfræðinga sem stýra Microsoft SQL Server, eru fáar viðvaranir sem valda jafn miklum kvíða og Villa 9002: The transaction log for database ‘X’ is full (Færsluskrá fyrir gagnagrunninn ‘X’ er full). Þegar færsluskráin fyllist og getur ekki stækkað, verður gagnagrunnurinn í raun skrifvarinn. Allar INSERT, UPDATE og DELETE aðgerðir stöðvast, færslur í forritum mistakast og framleiðsluumhverfið stöðvast algjörlega.
Að skilja undirliggjandi arkitektúr SQL Server færsluskrárinnar, greina rót vandans nákvæmlega og framkvæma skjót viðgerðarferli eru mikilvægir hæfileikar til að viðhalda mikilli framboðsgetu (high availability). Þessi ítarlega handbók kannar vélfræði færsluskrárinnar, hvernig á að leysa úr fullri skrá í neyðartilvikum og bestu starfsvenjur í arkitektúr til að koma í veg fyrir að þetta gerist aftur.
Skilningur á arkitektúr SQL Server færsluskrár
Til að leysa vandamál með fulla færsluskrá á áhrifaríkan hátt verður þú fyrst að skilja hvernig SQL Server skrifar og stýrir gögnum.
Write-Ahead Logging (WAL)
SQL Server notar Write-Ahead Logging (WAL) samskiptareglur. Hvenær sem gagnabreyting á sér stað er breytingin fyrst skrifuð í færsluskrána í minni, síðan skoluð yfir í líkamlegu skrána á disknum áður en raunverulegar gagnasíður eru uppfærðar í gagnagrunnsskránum (MDF/NDF). Þetta tryggir ACID (Atomicity, Consistency, Isolation, Durability) samræmi, sem tryggir að ef hrun verður geti SQL Server spilað aftur (roll forward) eða afturkallað (roll back) færslur.
Virtual Log Files (VLFs) og hringrásarskráning
Innan frá er líkamlega færsluskrárskráin (LDF) skipt í smærri, rökrétta hluta sem kallast Virtual Log Files (VLFs). Færsluskráin starfar í hringrás. Eftir því sem færsluskráarfærslur eru skrifaðar fylla þær eina VLF og færa sig yfir í þá næstu.
Þegar skráin nær enda líkamlegu skrárinnar reynir hún að byrja aftur á upphafinu. Hins vegar getur hún aðeins skrifað yfir VLF ef sú VLF er merkt sem óvirk (inactive). Ef allar VLFs eru virkar (sem þýðir að þær innihalda færslur sem SQL Server þarf enn á að halda), getur skráin ekki byrjað upp á nýtt. Ef sjálfvirk stækkun (auto-growth) er virkjuð og diskpláss er í boði, stækkar líkamlega skráin. Ef diskurinn er fullur eða sjálfvirk stækkun er takmörkuð, lendir þú í Villu 9002.
Færsluskráarhreinsun (Truncation) vs. Færsluskráarsamdráttur (Shrinking)
Algengur misskilningur er að hreinsun (truncation) færsluskrárinnar minnki líkamlega stærð skrárinnar.
* Færsluskráarhreinsun: Ferlið við að merkja virkar VLFs sem óvirkar, sem gerir plássið tiltækt til endurnotkunar. Það minnkar ekki stærð LDF-skrárinnar á disknum.
* Færsluskráarsamdráttur: Ferlið við að minnka líkamlega stærð LDF-skrárinnar og skila plássi til stýrikerfisins.
Í Full Recovery líkaninu á færsluskráarhreinsun aðeins sér stað þegar afritun færsluskrár (transaction log backup) hefur verið lokið með góðum árangri (að því gefnu að engin önnur ferli haldi skránni virkri).
Greining á „Transaction Log Full“ villunni (Villa 9002)
Þegar skráin er full er fyrsta skrefið ekki að bæta við diskplássi eða minnka skrár í blindni. Þú verður að bera kennsl á hvers vegna skráin getur ekki hreinsað sig. SQL Server býður upp á innbyggðan búnað til að segja þér nákvæmlega hvað kemur í veg fyrir endurnotkun skrárinnar í gegnum sys.databases vörulistasýnina.
Keyrðu eftirfarandi T-SQL skipun til að bera kennsl á flöskuhálsinn:
SELECT
name AS DatabaseName,
recovery_model_desc AS RecoveryModel,
log_reuse_wait_desc AS LogReuseWaitReason
FROM sys.databases
WHERE name = 'YourDatabaseName';
Þú getur líka athugað núverandi plássnotkun færsluskrárinnar með því að nota:
DBCC SQLPERF(LOGSPACE);
Algengar log_reuse_wait_desc stöður
- LOG_BACKUP: Gagnagrunnurinn er í Full eða Bulk-Logged endurheimtarlíkani og afritun færsluskrár hefur ekki verið tekin nýlega. Þetta er algengasta orsökin.
- ACTIVE_TRANSACTION: Langvarandi færsla (t.d. gríðarleg endurbygging á vísitölu eða gleymd óstaðfest færsla) heldur skránni virkri.
- REPLICATION / CDC: Transactional Replication eða Change Data Capture (CDC) er virkt og Log Reader Agent hefur ekki enn unnið úr færslunum.
- AVAILABILITY_REPLICA: Í AlwaysOn Availability Group er auka-afrit (secondary replica) aftengt eða samstillist of hægt, sem neyðir aðalafritið til að halda í færsluskráarfærslur þar til þær hafa verið staðfestar á auka-afritinu.
Skjótar viðgerðaraðferðir: Að leysa vandamálið í framleiðslu
Eftir því hvaða log_reuse_wait_desc er skilað, verða neyðarráðstafanir þínar mismunandi. Hér eru skjótar viðgerðaraðferðir fyrir algengustu aðstæður.
Aðstæður 1: Vantar afritun færsluskrár eða hún mistekst (LOG_BACKUP)
Ef biðtegundin er LOG_BACKUP er lausnin einföld: þú verður að taka afrit af færsluskránni.
BACKUP LOG [YourDatabaseName]
TO DISK = 'N:BackupsYourDatabaseName_EmergencyLog.trn'
WITH COMPRESSION, STATS = 10;
Þegar afrituninni er lokið verða óvirkar VLFs hreinsaðar og SQL Server mun halda áfram eðlilegri starfsemi. Ef afritunardiskurinn þinn er fullur gætirðu þurft að taka afrit á tímabundna netdrif eða null-tæki (ekki mælt með nema gagnagrunnurinn sé auðveldlega endurgeranlegur, þar sem það rýfur færslukeðjuna):
-- VIÐVÖRUN: Þetta rýfur færslukeðjuna og skerðir möguleika á endurheimt að ákveðnum tímapunkti.
-- Notaðu aðeins ef algjörlega nauðsynlegt og fylgdu strax eftir með FULLU afriti.
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';
Aðstæður 2: Langvarandi virkar færslur (ACTIVE_TRANSACTION)
Ef ein færsla hefur verið í gangi í marga klukkutíma kemur hún í veg fyrir hreinsun færsluskrárinnar allan þann tíma. Fyrst skaltu bera kennsl á færsluna sem veldur vandræðum:
DBCC OPENTRAN('YourDatabaseName');
Þessi skipun skilar elstu virku færslunni og Server Process ID (SPID) hennar. Þú getur safnað frekari upplýsingum um hvað SPID-ið er að gera með því að fyrirspyrja dynamic management views (DMVs):
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>;
Ef færslan er óæskileg fyrirspurn eða fast ferli gætirðu þurft að stöðva hana til að losa um skrána.
KILL <SPID>;
Athugið: Að stöðva (KILL) gríðarlega stóra færslu mun kalla fram afturköllun (rollback), sem getur tekið verulegan tíma og mun tímabundið búa til viðbótar færsluskráarvirkni. Ekki endurræsa SQL Server þjónustuna meðan á afturköllun stendur, annars mun gagnagrunnurinn fara í endurheimtarham við endurræsingu.
Aðstæður 3: Neyðarúthlutun plásss (Diskur er 100% fullur)
Ef LDF-skráin hefur eytt öllu plássi á drifinu geturðu ekki einu sinni keyrt afritun því SQL Server þarf smávegis af færsluskráarplássi til að skrá sjálfan afritunarviðburðinn. Í þessum aðstæðum verður þú að bæta við aukafærsluskrá á öðru drifi með lausu plássi.
ALTER DATABASE [YourDatabaseName]
ADD LOG FILE
(
NAME = N'YourDatabaseName_Log2',
FILENAME = N'E:TempLogsYourDatabaseName_Log2.ldf',
SIZE = 5GB,
MAXSIZE = 50GB,
FILEGROWTH = 1GB
);
Þetta veitir SQL Server strax svigrúm. Þegar gagnagrunnurinn er kominn á netið skaltu taka afrit af færsluskránni, tæma aukafærsluskrána og fjarlægja hana:
-- 1. Taktu afrit af færsluskrá til að hreinsa hana
BACKUP LOG [YourDatabaseName] TO DISK = '...';
-- 2. Tæmdu tímabundnu færsluskrána
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);
-- 3. Fjarlægðu tímabundnu færsluskrána
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];
Bestu starfsvenjur fyrir forvarnir og stjórnun færsluskrár
Viðbragðsmiðuð bilanaleit er streituvaldandi og hefur áhrif á þjónustustig (SLAs). Innleiðing á fyrirbyggjandi arkitektúr og rekstrarvenjum er nauðsynleg fyrir stöðugleika gagnagrunna í fyrirtækjum.
1. Innleiða öfluga, sjálfvirka afritunarstefnu
Ef gagnagrunnur er í Full recovery líkani er tíð afritun færsluskrár skylda. Það fer eftir Recovery Point Objective (RPO) og færslumagni, en afritun færsluskrár ætti að eiga sér stað á 5 til 15 mínútna fresti.
Afritunarlausnir fyrir fyrirtæki eins og CloudSave einfalda þetta ferli verulega. Með því að samþættast beint við SQL Server í gegnum VDI (Virtual Device Interface), gerir CloudSave gagnagrunnsstjórum kleift að stilla stefnumiðaðar, hátíðni afritanir á færsluskrám. Þetta tryggir að skrár séu stöðugt hreinsaðar, dulkóðaðar á öruggan hátt og geymdar utan staðsetningar eða í óbreytanlegri skýjageymslu, sem kemur í veg fyrir LOG_BACKUP biðstöðuna án þess að krefjast flókinna sérsniðinna SQL Agent verka.
2. Rétt stærð á færsluskrá og stjórnun VLFs
Að treysta á sjálfvirka stækkun (auto-growth) til að stjórna stærð færsluskrárinnar er hættulegt mynstur. Sjálfvirkar stækkunaraðgerðir eru dýrar og gera hlé á vinnslu færslna á meðan diskurinn er núllstilltur (nema Instant File Initialization sé virkt, sem á ekki við um færsluskrár).
Ennfremur leiða tíðar, litlar sjálfvirkar stækkanir (t.d. að stækka um 10% eða 50MB í einu) til VLF-brotunar. Færsluskrá með þúsundum pínulitlum VLFs mun draga verulega úr ræsingartíma gagnagrunns, afritunarafköstum og töfum á afritun (replication latency).
- Forstilltu stærð skrárinnar: Greindu stærstu viðhaldsaðgerðir þínar (eins og endurbyggingu vísitalna) og forstilltu stærð LDF-skrárinnar til að rúma þær án þess að þurfa að stækka.
- Stilltu fasta sjálfvirka stækkun: Breyttu sjálfvirkri stækkun úr hlutfalli í fasta stærð (t.d. 1GB eða 5GB) til að tryggja að VLFs séu búnar til í heilbrigðri stærð.
Þú getur athugað VLF-fjölda þinn með eftirfarandi fyrirspurn (fyrir 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'));
Ef VLF-fjöldi þinn er yfir 500, íhugaðu að bíða eftir rólegu tímabili, minnka skrána í lágmarksstærð og stækka hana handvirkt aftur í nauðsynlega stærð í stórum skömmtum.
3. Fínstilltu viðhaldsaðgerðir vísitalna
Endurbygging vísitalna (Index rebuilds) eru aðgerðir sem eru að fullu skráðar í færsluskrána, jafnvel í Bulk-Logged recovery líkaninu (fer eftir tegund vísitölu). Að endurbyggja 500GB vísitölu mun búa til að minnsta kosti 500GB af færsluskráarfærslum.
Til að draga úr færsluskráarþenslu við viðhald:
* Notaðu SORT_IN_TEMPDB = ON þegar þú endurbyggir vísitölur. Þetta færir flokkunarfasann yfir í TempDB, sem dregur úr álagi á færsluskrá notendagagnagrunnsins.
* Skiptu úr endurbyggingu vísitalna (rebuilds) yfir í endurskipulagningu vísitalna (reorganizes) þar sem því verður við komið, þar sem endurskipulagning er skilvirkari fyrir færsluskrána og hægt er að rjúfa hana án þess að afturkalla alla aðgerðina.
* Skiptu stórum DELETE eða UPDATE aðgerðum í lotur. Í stað þess að eyða 10 milljónum raða í einni færslu, eyddu þeim í 50.000 raða lotum, staðfestu (commit) og leyfðu afritun færsluskrár að hreinsa skrána á milli lota.
4. Fylgstu með mikilli framboðsgetu og afritunartópólógíum
Í AlwaysOn Availability Groups getur aðalafritið ekki hreinsað skrána sína fyrr en færsluskráarfærslurnar hafa verið staðfestar á öllum samstilltum og ósamstilltum auka-afritum.
Ef auka-afrit fer af netinu, eða ef netbandbreiddin nær ekki að halda í við færslumyndunarhraða aðalafritsins, mun sendibiðröð aðalafritsins stækka og skráin mun fyllast (AVAILABILITY_REPLICA biðtegund).
Innleiddu öflugt eftirlit fyrir SQLServer:Replica > Log Send Queue afkastavísinn. Ef auka-afrit tapast varanlega verður þú að fjarlægja það úr Availability Group eða stöðva gagnaflutning til að leyfa aðalfærsluskránni að hreinsast.
Niðurstaða
Að lenda í fullri færsluskrá er eins konar eldskírn fyrir gagnagrunnsstjóra, en það þarf ekki að leiða til langvarandi niðurtíma. Með því að skilja vélfræði Write-Ahead Logging og VLFs geturðu fljótt greint rót vandans með sys.databases og beitt réttri skjótri viðgerðaraðferð.
Stöðugleiki til langs tíma byggist á því að hverfa frá viðbragðsmiðuðum lagfæringum. Að forstilla stærð færsluskrár, fínstilla viðhaldsrútínur og nýta afritunarvettvanga fyrir fyrirtæki eins og CloudSave til að framfylgja ströngum, sjálfvirkum afritunaráætlunum mun tryggja að færsluskrárnar þínar haldist heilbrigðar, hreinsaðar og tilbúnar til að styðja við framleiðsluálag með mikilli afkastagetu.
