Por Database Administrators (DBA-oj) kaj DevOps-inĝenieroj administrantaj Microsoft SQL Server, malmultaj atentigoj kaŭzas tiom da tuja angoro kiel Eraro 9002: La transakcia protokolo por datumbazo ‘X’ estas plena. Kiam la transakcia protokolo pleniĝas kaj ne povas kreski, la datumbazo efike fariĝas nur-lega. Ĉiuj INSERT, UPDATE, kaj DELETE operacioj haltas, aplikaĵaj transakcioj malsukcesas, kaj produktado tute haltas.
Kompreni la suban arkitekturon de la SQL Server-transakcia protokolo, precize diagnozi la radikan kaŭzon, kaj plenumi rapidajn reakirajn procedurojn estas kritikaj kapabloj por konservi altan haveblecon. Ĉi tiu ampleksa gvidilo esploras la mekanikojn de la transakcia protokolo, kiel solvi plenan protokolon en kriz-okazo, kaj arkitekturajn plej bonajn praktikojn por malhelpi ĝin denove okazi.
Komprenante la Arkitekturon de SQL Server-Transakcia Protokolo
Por efike solvi problemon de plena transakcia protokolo, vi unue devas kompreni kiel SQL Server skribas kaj administras datumojn.
Write-Ahead Logging (WAL)
SQL Server uzas protokolon de Write-Ahead Logging (WAL). Kiam ajn okazas datuma modifo, la ŝanĝo estas unue skribita al la transakcia protokolo en memoro, poste elverŝita al la fizika protokoldosiero sur disko antaŭ ol la faktaj datumaj paĝoj estas ĝisdatigitaj en la datumbazaj dosieroj (MDF/NDF). Ĉi tio garantias ACID (Atomikeco, Konsistenco, Izoliteco, Fortikeco) plenumon, certigante ke en kazo de kraŝo, SQL Server povas reludi (ruli antaŭen) aŭ malfari (ruli malantaŭen) transakciojn.
Virtualaj Protokoldosieroj (VLF-oj) kaj Cirkulera Protokolado
Interne, la fizika transakcia protokoldosiero (LDF) estas dividita en pli malgrandajn, logikajn segmentojn nomitajn Virtualaj Protokoldosieroj (VLF-oj). La transakcia protokolo funkcias cirkulere. Dum protokolaj rekordoj estas skribitaj, ili plenigas unu VLF-on kaj moviĝas al la sekva.
Kiam la protokolo atingas la finon de la fizika dosiero, ĝi provas reveni al la komenco. Tamen, ĝi povas nur superverki VLF-on se tiu VLF estas markita kiel neaktiva. Se ĉiuj VLF-oj estas aktivaj (signifante ke ili enhavas protokolajn rekordojn ankoraŭ bezonatajn de SQL Server), la protokolo ne povas reveni. Se aŭtomata kresko estas ebligita kaj diskospaco estas disponebla, la fizika dosiero kreskas. Se la disko estas plena aŭ aŭtomata kresko estas limigita, vi renkontas Eraron 9002.
Protokola Tronkado vs. Protokola Malgrandigo
Ofta miskompreno estas ke tronkado de la protokolo reduktas la fizikan dosiergrandecon.
* Protokola Tronkado: La procezo de markado de aktivaj VLF-oj kiel neaktivaj, igante la spacon disponebla por reuzo. Ĝi ne reduktas la grandecon de la LDF-dosiero sur disko.
* Protokola Malgrandigo: La procezo de fizike redukti la LDF-dosiergrandecon kaj redoni spacon al la operaciumo.
En la Plena Reakira modelo, protokola tronkado nur okazas kiam transakcia protokola sekurkopio estas sukcese kompletigita (supozante ke neniuj aliaj procezoj tenas la protokolon aktiva).
Diagnozante la Eraron “Transakcia Protokolo Plena” (Eraro 9002)
Kiam la protokolo estas plena, via unua paŝo ne estas blinde aldoni diskospacon aŭ malgrandigi dosierojn. Vi devas identigi kial la protokolo ne povas tronki. SQL Server provizas enkonstruitan mekanismon por diri al vi precize kio malhelpas protokolan reuzon per la sys.databases kataloga vido.
Rulu la sekvan T-SQL-komandon por identigi la botelkolon:
SELECT
name AS DatabaseName,
recovery_model_desc AS RecoveryModel,
log_reuse_wait_desc AS LogReuseWaitReason
FROM sys.databases
WHERE name = 'YourDatabaseName';
Vi ankaŭ povas kontroli la nunan spacuzadon de viaj transakciaj protokoloj uzante:
DBCC SQLPERF(LOGSPACE);
Oftaj log_reuse_wait_desc Statoj
- LOG_BACKUP: La datumbazo estas en la Plena aŭ Bulk-Logged reakira modelo, kaj transakcia protokola sekurkopio ne estis farita lastatempe. Ĉi tio estas la plej ofta kaŭzo.
- ACTIVE_TRANSACTION: Longdaŭra transakcio (ekz. masiva indeksa rekonstruo aŭ forgesita nekomitita transakcio) tenas la protokolon aktiva.
- REPLICATION / CDC: Transakcia Repliko aŭ Change Data Capture (CDC) estas ebligita, kaj la Log Reader Agent ankoraŭ ne procesis la transakciojn.
- AVAILABILITY_REPLICA: En AlwaysOn Availability Group, sekundara repliko estas malkonektita aŭ sinkronigas tro malrapide, devigante la primaran replikon reteni protokolajn rekordojn ĝis ili estas harditaj sur la sekundara.
Rapidaj Reakiraj Strategioj: Solvante la Problemon en Produktado
Depende de la log_reuse_wait_desc redonita, via kriz-respondo varias. Jen la rapidaj reakiraj strategioj por la plej oftaj scenaroj.
Scenaro 1: Mankantaj aŭ Malsukcesantaj Protokolaj Sekurkopioj (LOG_BACKUP)
Se la atendotipo estas LOG_BACKUP, la solvo estas simpla: vi devas sekurkopii la transakcian protokolon.
BACKUP LOG [YourDatabaseName]
TO DISK = 'N:BackupsYourDatabaseName_EmergencyLog.trn'
WITH COMPRESSION, STATS = 10;
Post kiam la sekurkopio finiĝas, la neaktivaj VLF-oj estos tronkitaj, kaj SQL Server rekomencos normalajn operaciojn. Se via sekurkopia disko estas plena, vi eble bezonos sekurkopii al provizora ret-parto aŭ nula aparato (tre malkonsilite krom se la datumbazo estas facile reproduktebla, ĉar ĝi rompas la protokolan ĉenon):
-- AVERTO: Ĉi tio rompas la protokolan ĉenon kaj kompromitas punkt-en-tempan reakiron.
-- Uzu nur se absolute necese kaj sekvu tuj per PLENA sekurkopio.
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';
Scenaro 2: Longdaŭraj Aktivaj Transakcioj (ACTIVE_TRANSACTION)
Se ununura transakcio funkcias dum horoj, ĝi malhelpas protokolan tronkadon por la tuta daŭro. Unue, identigu la ofendan transakcion:
DBCC OPENTRAN('YourDatabaseName');
Ĉi tiu komando redonas la plej malnovan aktivan transakcion kaj ĝian Server Process ID (SPID). Vi povas kolekti pliajn detalojn pri tio, kion la SPID faras, per pridemandado de dinamikaj administraj vidpunktoj (DMV-oj):
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>;
Se la transakcio estas fripona demando aŭ haltita procezo, vi eble bezonos ĉesigi ĝin por liberigi la protokolon.
KILL <SPID>;
Noto: Mortigi masivan transakcion ekigos ruli-malantaŭen, kio povas preni signifan tempon kaj provizore generos plian protokolan agadon. Ne rekomencu la SQL Server-servon dum ruli-malantaŭen, alie la datumbazo eniros reakiran reĝimon post rekomenco.
Scenaro 3: Kriz-Spaca Asigno (Disko estas 100% Plena)
Se la LDF-dosiero konsumis la tutan diskon, vi eĉ ne povas ruli sekurkopion ĉar SQL Server postulas malgrandan kvanton da protokola spaco por registri la sekurkopian eventon mem. En ĉi tiu scenaro, vi devas aldoni sekundaran protokoldosieron sur malsama disko kun disponebla spaco.
ALTER DATABASE [YourDatabaseName]
ADD LOG FILE
(
NAME = N'YourDatabaseName_Log2',
FILENAME = N'E:TempLogsYourDatabaseName_Log2.ldf',
SIZE = 5GB,
MAXSIZE = 50GB,
FILEGROWTH = 1GB
);
Ĉi tio tuj provizas al SQL Server spacon por spiri. Post kiam la datumbazo estas interrete, faru transakcian protokolan sekurkopion, malplenigu la sekundaran protokoldosieron, kaj forigu ĝin:
-- 1. Faru protokolan sekurkopion por tronki la protokolon
BACKUP LOG [YourDatabaseName] TO DISK = '...';
-- 2. Malplenigu la provizoran protokoldosieron
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);
-- 3. Forigu la provizoran protokoldosieron
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];
Plej bonaj Praktikoj por Transakcia Protokola Preventado kaj Administrado
Reaktiva solvado de problemoj estas streĉa kaj influas SLA-ojn. Efektivigi iniciatemajn arkitekturajn kaj operaciajn plej bonajn praktikojn estas esenca por entreprena datumbaza stabileco.
1. Efektivigu Fortikan, Aŭtomatigitan Sekurkopian Strategion
Se datumbazo estas en la Plena reakira modelo, oftaj transakciaj protokolaj sekurkopioj estas devigaj. Depende de via Reakira Punkta Celo (RPO) kaj transakcia volumo, protokolaj sekurkopioj devus okazi ĉiujn 5 ĝis 15 minutojn.
Entreprenaj sekurkopiaj solvoj kiel CloudSave simpligas ĉi tiun procezon signife. Integrante rekte kun SQL Server per VDI (Virtual Device Interface), CloudSave permesas al DBA-oj agordi politiko-movitajn, alt-frekvencajn transakciajn protokolajn sekurkopiojn. Ĉi tio certigas, ke protokoloj estas kontinue tronkitaj, sekure ĉifritaj, kaj stokitaj ekster-loke aŭ en neŝanĝebla nuba stokado, malhelpante la LOG_BACKUP atendotipon sen bezoni kompleksajn kutimajn SQL Agent-laborojn.
2. Ĝuste Grandigu la Transakcian Protokolon kaj Administru VLF-ojn
Fidi je aŭtomata kresko por administri vian transakcian protokolan grandecon estas danĝera kontraŭ-ŝablono. Aŭtomataj kreskaj operacioj estas multekostaj kaj paŭzas transakcian prilaboradon dum la disko estas nul-inicialigita (krom se Instant File Initialization estas ebligita, kio ne validas por protokoldosieroj).
Krome, oftaj, malgrandaj aŭtomataj kreskoj (ekz. kreskado je 10% aŭ 50MB samtempe) kondukas al VLF-fragmentiĝo. Transakcia protokolo kun miloj da malgrandaj VLF-oj grave degradigos datumbazajn starttempojn, sekurkopian rendimenton, kaj replikan latentecon.
- Antaŭ-grandigu la protokolon: Analizu viajn plej grandajn prizorgajn operaciojn (kiel indeksaj rekonstruoj) kaj antaŭ-grandigu la LDF-dosieron por akomodi ilin sen kreskado.
- Agordu fiksan aŭtomatan kreskon: Ŝanĝu aŭtomatan kreskon de procento al fiksa grandeco (ekz. 1GB aŭ 5GB) por certigi, ke VLF-oj estas kreitaj je sana grandeco.
Vi povas kontroli vian VLF-kalkulon uzante la sekvan demandon (por 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'));
Se via VLF-kalkulo estas super 500, konsideru atendi trankvilan periodon, malgrandigi la protokolon al minimuma grandeco, kaj mane kreskigi ĝin reen al ĝia bezonata grandeco en grandaj pecoj.
3. Optimumigu Indeksajn Prizorgajn Operaciojn
Indeksaj rekonstruoj estas plene protokolitaj operacioj, eĉ en la Bulk-Logged reakira modelo (depende de la indeksa tipo). Rekonstrui 500GB-an indekson generos almenaŭ 500GB da transakciaj protokolaj rekordoj.
Por mildigi protokolan ŝveladon dum prizorgado:
* Uzu SORT_IN_TEMPDB = ON kiam vi rekonstruas indeksojn. Ĉi tio forŝarĝas la ordigan fazon al TempDB, reduktante la ŝarĝon sur la transakcia protokolo de la uzantdatumbazo.
* Ŝanĝu de indeksa rekonstruo al indeksa reorganizigo kie eble, ĉar reorganizigoj estas pli protokole efikaj kaj povas esti interrompitaj sen ruli-malantaŭen la tutan operacion.
* Pakigu grandajn DELETE aŭ UPDATE operaciojn. Anstataŭ forigi 10 milionojn da vicoj en unu transakcio, forigu ilin en pakoj de 50,000, komitante kaj permesante al protokolaj sekurkopioj tronki la protokolon inter pakoj.
4. Monitoru Alt-Haveblecon kaj Replikajn Topologiojn
En AlwaysOn Availability Groups, la primara repliko ne povas tronki sian protokolon ĝis la protokolaj rekordoj estis harditaj sur ĉiuj sinkronaj kaj nesinkronaj sekundaraj replikoj.
Se sekundara repliko iĝas eksterrete, aŭ se la retbando ne povas sekvi la transakcian generadrapidecon de la primara, la send-vico de la primara kreskos, kaj la protokolo pleniĝos (AVAILABILITY_REPLICA atendotipo).
Efektivigu fortikan monitoradon por la SQLServer:Replica > Log Send Queue rendimenta nombrilo. Se sekundara repliko estas konstante perdita, vi devas forigi ĝin de la Availability Group aŭ suspendi datuman movadon por permesi al la primara protokolo tronki.
Konkludo
Renkonti plenan transakcian protokolon estas inicado por datumbazaj administrantoj, sed ĝi ne devas rezultigi longedaŭran malfunkcion. Komprenante la mekanikojn de Write-Ahead Logging kaj VLF-oj, vi povas rapide diagnozi la radikan kaŭzon uzante sys.databases kaj apliki la ĝustan rapidan reakiran strategion.
Longtempa stabileco dependas de foriro de reaktivaj solvoj. Antaŭ-grandigi viajn protokoldosierojn, optimumigi prizorgajn rutinojn, kaj uzi entrepren-nivelajn sekurkopiajn platformojn kiel CloudSave por devigi striktajn, aŭtomatigitajn protokoldosierajn sekurkopiajn horarojn certigos, ke viaj transakciaj protokoloj restas sanaj, tronkitaj, kaj pretaj subteni alt-trafluajn produktadajn laborŝarĝojn.
