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.

Kubaphathi be-Database (DBAs) kanye nonjiniyela be-DevOps abaphatha i-Microsoft SQL Server, izexwayiso ezimbalwa ezibangela ukukhathazeka okusheshayo njengephutha 9002: I-transaction log yedathabheyisi ‘X’ igcwele. Uma i-transaction log igcwala futhi ingakwazi ukukhula, idathabheyisi iba ngeyokufunda kuphela (read-only). Yonke imisebenzi ye-INSERT, UPDATE, kanye ne-DELETE iyama, ama-transaction ezinhlelo ayahluleka, futhi ukusebenza kwenkampani kuyema nya.

Ukuqonda ukwakheka okungaphansi kwe-SQL Server transaction log, ukuxilonga ngokunembile imbangela, nokwenza izinqubo zokubuyisela ezisheshayo kuyamakhono abalulekile ekugcineni ukutholakala okuphezulu (high availability). Lo mhlahlandlela ophelele uhlola indlela i-transaction log esebenza ngayo, indlela yokuxazulula ilogi egcwele esimweni esiphuthumayo, kanye nezindlela ezinhle kakhulu zokuvimbela ukuthi lokhu kungenzeki futhi.

Ukuqonda Ukwakheka kwe-SQL Server Transaction Log

Ukuze uxazulule inkinga ye-transaction log egcwele ngempumelelo, kufanele uqale uqonde ukuthi i-SQL Server ibhala futhi iphathe kanjani idatha.

I-Write-Ahead Logging (WAL)

I-SQL Server isebenzisa umthetho we-Write-Ahead Logging (WAL). Noma nini lapho kukhona ukushintshwa kwedatha okwenzekayo, ushintsho luqala lubhalwe ku-transaction log enkumbulweni (memory), bese luthunyelwa kufayela lelogi elisemzimbeni (physical log file) kudiski ngaphambi kokuba amakhasi edatha wangempela abuyekezwe kumafayela edathabheyisi (MDF/NDF). Lokhu kuqinisekisa ukuthobela i-ACID (Atomicity, Consistency, Isolation, Durability), okuqinisekisa ukuthi uma kwenzeka ukuphahlazeka, i-SQL Server ingaphinda (roll forward) noma ihlehlise (roll back) ama-transaction.

Ama-Virtual Log Files (VLFs) kanye ne-Circular Logging

Ngaphakathi, ifayela le-transaction log elisemzimbeni (LDF) lihlukaniswe ngezingxenye ezincane, ezinengqondo ezibizwa ngokuthi ama-Virtual Log Files (VLFs). I-transaction log isebenza ngendlela eyindilinga. Njengoba amarekhodi elogi ebhaliwe, agcwalisa i-VLF eyodwa bese edlulela kwelandelayo.

Lapho ilogi ifika ekupheleni kwefayela elisemzimbeni, izama ukubuyela ekuqaleni. Nokho, ingabhala phezu kwe-VLF kuphela uma leyo VLF imakwe njenge-inactive. Uma wonke ama-VLF esebenza (okusho ukuthi aqukethe amarekhodi elogi asadingeka yi-SQL Server), ilogi ayikwazi ukubuyela emuva. Uma ukukhula okuzenzakalelayo (auto-growth) kuvuliwe futhi kukhona isikhala kudiski, ifayela elisemzimbeni liyakhula. Uma idiski igcwele noma ukukhula okuzenzakalelayo kuvinjelwe, uzohlangabezana nephutha 9002.

I-Log Truncation vs. I-Log Shrinking

Umbono okhona ovamile ukuthi ukusika (truncating) ilogi kunciphisa usayizi wefayela elisemzimbeni.
* I-Log Truncation: Inqubo yokumaka ama-VLF asebenzayo njengangasebenzi, okwenza isikhala sitholakale ukuze siphinde sisetshenziswe. Lokhu akunciphisi usayizi wefayela le-LDF kudiski.
* I-Log Shrinking: Inqubo yokunciphisa ngokomzimba usayizi wefayela le-LDF nokubuyisela isikhala ohlelweni lokusebenza (operating system).

Kumodeli ye-Full Recovery, i-log truncation yenzeka kuphela uma i-backup ye-transaction log iqedwe ngempumelelo (kucatshangelwa ukuthi azikho ezinye izinqubo ezibambe ilogi).

Ukuxilonga Iphutha le-“Transaction Log Full” (Iphutha 9002)

Lapho ilogi igcwele, isinyathelo sakho sokuqala akufanele kube ukungeza isikhala kudiski noma ukunciphisa amafayela ngokungacabangi. Kufanele uhlonze ukuthi kungani ilogi ingakwazi ukusikwa. I-SQL Server inikeza indlela eyakhelwe ngaphakathi yokukutshela ngqo ukuthi yini evimbela ukusetshenziswa kabusha kwelogi nge-sys.databases catalog view.

Sebenzisa umyalo we-T-SQL olandelayo ukuze uhlonze inkinga:

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

Ungaphinda uhlole ukusetshenziswa kwesikhala kwamanje kwama-transaction log akho usebenzisa:

DBCC SQLPERF(LOGSPACE);

Izimo ezivamile ze-log_reuse_wait_desc

  1. LOG_BACKUP: Idathabheyisi ikumodeli ye-Full noma ye-Bulk-Logged recovery, futhi i-backup ye-transaction log ayithathwanga muva nje. Lesi yimbangela evame kakhulu.
  2. ACTIVE_TRANSACTION: I-transaction esebenza isikhathi eside (isb., ukwakhiwa kabusha kwenkomba enkulu noma i-transaction engaqediwe ekhohliwe) igcina ilogi isebenza.
  3. REPLICATION / CDC: I-Transactional Replication noma i-Change Data Capture (CDC) ivuliwe, futhi i-Log Reader Agent ayikawacubunguli ama-transaction.
  4. AVAILABILITY_REPLICA: Ku-AlwaysOn Availability Group, i-replica yesibili inqanyuliwe noma ivumelanisa kancane kakhulu, okuphoqa i-replica eyinhloko ukuthi igcine amarekhodi elogi kuze kube yilapho aqiniswa ku-replica yesibili.

Amasu Okubuyisela Asheshayo: Ukuxazulula Inkinga Ekukhiqizeni

Ngokuya nge-log_reuse_wait_desc ebuyisiwe, impendulo yakho yezimo eziphuthumayo izohluka. Nanka amasu okubuyisela asheshayo ezimweni ezivame kakhulu.

Isimo 1: Ama-backup elogi alahlekile noma ahlulekayo (LOG_BACKUP)

Uma uhlobo lokulinda kungu-LOG_BACKUP, isixazululo siqondile: kufanele wenze i-backup ye-transaction log.

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

Uma i-backup isiqediwe, ama-VLF angasebenzi azosikwa, futhi i-SQL Server izoqhubeka nemisebenzi evamile. Uma idrayivu yakho ye-backup igcwele, kungase kudingeke ukuthi wenze i-backup kwisabelo senethiwekhi sesikhashana noma kudivayisi ye-null (akukhuthazwa ngaphandle uma idathabheyisi kulula ukuyikhiqiza kabusha, ngoba lokhu kwephula iketango lelogi):

-- ISEXYWAYISO: Lokhu kwephula iketango lelogi futhi kuphazamisa ukubuyiselwa kwesikhathi esithile.
-- Sebenzisa kuphela uma kudingeka impela futhi ulandele ngokushesha nge-FULL backup.
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';

Isimo 2: Ama-transaction asebenza isikhathi eside (ACTIVE_TRANSACTION)

Uma i-transaction eyodwa isebenze amahora amaningi, ivimbela ukusikwa kwelogi ngaso sonke isikhathi. Okokuqala, hlonza i-transaction ebangela inkinga:

DBCC OPENTRAN('YourDatabaseName');

Lo myalo ubuyisela i-transaction endala esebenzayo kanye ne-Server Process ID (SPID) yayo. Ungaqoqa imininingwane eyengeziwe mayelana nokuthi i-SPID yenzani ngokubuza ama-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>;

Uma i-transaction iwumbuzo ongalungile noma inqubo emile, kungase kudingeke ukuthi uyinqamule ukuze ukhulule ilogi.

KILL <SPID>;

Qaphela: Ukunqamula i-transaction enkulu kuzobangela ukuhlehla (rollback), okungathatha isikhathi esiningi futhi kuzokhiqiza umsebenzi owengeziwe welogi okwesikhashana. Ungayiqali kabusha insizakalo ye-SQL Server phakathi nokuhlehla, kungenjalo idathabheyisi izongena kumodi yokubuyisela lapho iqala kabusha.

Isimo 3: Ukwabiwa kwesikhala esiphuthumayo (Idiski igcwele ngo-100%)

Uma ifayela le-LDF lidle idrayivu yonke, awukwazi ngisho nokwenza i-backup ngoba i-SQL Server idinga isikhala esincane selogi ukuze irekhode umcimbi we-backup uqobo. Kulesi simo, kufanele ungeze ifayela lelogi lesibili kwidrayivu ehlukile enesikhala esitholakalayo.

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

Lokhu kunikeza i-SQL Server indawo yokuphefumula ngokushesha. Uma idathabheyisi isiku-inthanethi, yenza i-backup ye-transaction log, thulula ifayela lelogi lesibili, bese ususa lona:

-- 1. Yenza i-log backup ukuze usike ilogi
BACKUP LOG [YourDatabaseName] TO DISK = '...';

-- 2. Thulula ifayela lelogi lesikhashana
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);

-- 3. Susa ifayela lelogi lesikhashana
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];

Izindlela Ezinhle Zokuvimbela Nokuphatha i-Transaction Log

Ukuxazulula izinkinga ngemuva kokuba zenzekile kuyakhathaza futhi kuthinta ama-SLA. Ukusebenzisa izindlela ezinhle zokwakha nezokusebenza kubalulekile ekuzinzeni kwedathabheyisi yebhizinisi.

1. Sebenzisa Isu le-Backup eliqinile nelizenzakalelayo

Uma idathabheyisi ikumodeli ye-Full recovery, ama-backup e-transaction log avamile ayimpoqo. Ngokuya nge-Recovery Point Objective (RPO) yakho kanye nomthamo we-transaction, ama-backup elogi kufanele enzeke njalo ngemizuzu emi-5 kuya kwengu-15.

Izixazululo ze-backup zebhizinisi ezifana ne-CloudSave zenza le nqubo ibe lula kakhulu. Ngokuhlanganisa ngqo ne-SQL Server nge-VDI (Virtual Device Interface), i-CloudSave ivumela ama-DBA ukuthi alungiselele ama-backup e-transaction log avamile, aqhutshwa yinqubomgomo. Lokhu kuqinisekisa ukuthi ama-log asikwa njalo, abethelwe ngokuphephile, futhi agcinwe endaweni ekude noma kwisitoreji sefu esingenakushintshwa, kuvimbele isimo sokulinda se-LOG_BACKUP ngaphandle kokudinga imisebenzi eyinkimbinkimbi ye-SQL Agent.

2. Lungisa Usayizi we-Transaction Log futhi uphathe ama-VLF

Ukuthembela ekukhuleni okuzenzakalelayo (auto-growth) ukuze uphathe usayizi we-transaction log yakho kuyindlela eyingozi. Imisebenzi yokukhula okuzenzakalelayo iyabiza futhi imisa ukucubungulwa kwe-transaction ngenkathi idiski iqalwa kabusha (ngaphandle uma i-Instant File Initialization ivuliwe, okungasebenzi kumafayela elogi).

Ngaphezu kwalokho, ukukhula okuzenzakalelayo okuvamile, okuncane (isb., ukukhula ngo-10% noma 50MB ngesikhathi) kuholela ku-VLF fragmentation. I-transaction log enezinkulungwane zama-VLF amancane izonciphisa kakhulu izikhathi zokuqala kwedathabheyisi, ukusebenza kwe-backup, kanye ne-replication latency.

  • Pre-size ilogi: Hlaziya imisebenzi yakho yokunakekela enkulu (njengokwakhiwa kabusha kwenkomba) futhi usayizi wefayela le-LDF ube ngaphambi kwesikhathi ukuze uwamukele ngaphandle kokukhula.
  • Setha ukukhula okuzenzakalelayo okungaguquki: Shintsha ukukhula okuzenzakalelayo kusuka ephesentini uye kusayizi ongaguquki (isb., 1GB noma 5GB) ukuze uqinisekise ukuthi ama-VLF adalwa ngosayizi onempilo.

Ungahlola inani lakho le-VLF usebenzisa umbuzo olandelayo (we-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'));

Uma inani lakho le-VLF lingaphezu kuka-500, cabanga ukulinda isikhathi esithulile, unciphise ilogi ibe usayizi omncane, bese uyikhulisa ngesandla ubuyele kusayizi wayo odingekayo ngezingxenye ezinkulu.

3. Lungiselela Imisebenzi Yokunakekela Inkomba

Ukwakhiwa kabusha kwenkomba (Index rebuilds) kuyimisebenzi ebhalwe ngokugcwele, ngisho nakumodeli ye-Bulk-Logged recovery (ngokuya ngohlobo lwenkomba). Ukwakha kabusha inkomba engu-500GB kuzokhiqiza okungenani ama-500GB amarekhodi e-transaction log.

Ukuze unciphise ukugcwala kwelogi phakathi nokunakekela:
* Sebenzisa SORT_IN_TEMPDB = ON lapho wakha kabusha izinkomba. Lokhu kuthuthela isigaba sokuhlunga ku-TempDB, kunciphise umthwalo ku-transaction log yedathabheyisi yomsebenzisi.
* Shintsha kusuka ekwakhiweni kabusha kwenkomba (index rebuilds) uye ekuhleleni kabusha kwenkomba (index reorganizes) lapho kungenzeka khona, njengoba ukuhlelwa kabusha kusebenza kahle kakhulu ngelogi futhi kungaphazamiseka ngaphandle kokuhlehla wonke umsebenzi.
* Hlela imisebenzi emikhulu ye-DELETE noma ye-UPDATE. Esikhundleni sokususa imigqa eyizigidi eziyi-10 ku-transaction eyodwa, isuse ngezingxenye ezingama-50,000, uqede futhi uvumele ama-backup elogi ukuthi asike ilogi phakathi kwamaqoqo.

4. Qapha i-High Availability kanye ne-Replication Topologies

Ku-AlwaysOn Availability Groups, i-replica eyinhloko ayikwazi ukusika ilogi yayo kuze kube yilapho amarekhodi elogi eqiniswe kuwo wonke ama-replica esibili avumelanayo (synchronous) nange-asynchronous.

Uma i-replica yesibili ingasebenzi, noma uma umkhawulokudonsa wenethiwekhi ungakwazi ukuhambisana nezinga lokukhiqizwa kwe-transaction okuyinhloko, umugqa wokuthumela (send queue) oyinhloko uzokhula, futhi ilogi izogcwala (uhlobo lokulinda lwe-AVAILABILITY_REPLICA).

Sebenzisa ukuqapha okuqinile kwe-SQLServer:Replica > Log Send Queue performance counter. Uma i-replica yesibili ilahleke unomphela, kufanele uyisuse ku-Availability Group noma umise ukunyakaza kwedatha ukuze uvumele ilogi eyinhloko ukuthi isikwe.

Isiphetho

Ukuhlangabezana ne-transaction log egcwele kuyisivivinyo kubaphathi bedathabheyisi, kodwa akudingeki ukuthi kuholele ekumeni okude. Ngokuqonda indlela i-Write-Ahead Logging nama-VLF asebenza ngayo, ungaxilonga ngokushesha imbangela usebenzisa i-sys.databases futhi usebenzise isu elilungile lokubuyisela ngokushesha.

Ukuzinza kwesikhathi eside kuncike ekusukeni ekulungiseni izinkinga ngemuva kokuba zenzekile. Ukubeka usayizi wamafayela elogi akho ngaphambi kwesikhathi, ukulungiselela izinqubo zokunakekela, nokusebenzisa izinkundla ze-backup zebhizinisi ezifana ne-CloudSave ukuze uphoqe amashejuli e-backup elogi aqinile, azenzakalelayo kuzoqinisekisa ukuthi ama-transaction log akho ahlala enempilo, esikiwe, futhi elungele ukusekela imisebenzi yokukhiqiza ephezulu.

Categories