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.

Maamulka Database-yada (DBAs) iyo injineerada DevOps ee maamula Microsoft SQL Server, digniin yar ayaa keenta walaac degdeg ah sida Khaladka 9002: Diiwaanka macaamilka (transaction log) ee database-ka ‘X’ waa buuxa. Marka diiwaanka macaamilku buuxsamo oo uusan korin karin, database-ku wuxuu noqonayaa mid la akhriyi karo oo keliya (read-only). Dhammaan hawlaha INSERT, UPDATE, iyo DELETE way istaagayaan, macaamilka codsiyada (application transactions) way fashilmayaan, wax soo saarkuna wuu istaagayaa.

Fahamka qaab-dhismeedka hoose ee diiwaanka macaamilka ee SQL Server, ogaanshaha saxda ah ee sababta asaasiga ah, iyo fulinta hababka soo kabashada degdegga ah waa xirfado muhiim u ah ilaalinta helitaanka sare (high availability). Hagahan dhammaystiran wuxuu sahaminayaa makaanikada diiwaanka macaamilka, sida loo xalliyo diiwaanka buuxa xilliga degdegga ah, iyo hababka ugu wanaagsan ee qaab-dhismeedka si looga hortago inay mar kale dhacdo.

Fahamka Qaab-dhismeedka Diiwaanka Macaamilka ee SQL Server

Si aad si wax ku ool ah u xalliso diiwaanka macaamilka ee buuxa, waa inaad marka hore fahantaa sida SQL Server u qoro oo u maamulo xogta.

Qorista-Hore ee Diiwaanka (Write-Ahead Logging – WAL)

SQL Server wuxuu isticmaalaa hab-maamuuska Qorista-Hore ee Diiwaanka (WAL). Mar kasta oo wax ka beddel xogeed dhaco, isbeddelka waxaa marka hore lagu qoraa diiwaanka macaamilka ee xusuusta (memory), ka dibna waxaa loo wareejiyaa faylka diiwaanka ee diskiga ka hor inta aan bogagga xogta dhabta ah lagu cusboonaysiin faylasha database-ka (MDF/NDF). Tani waxay dammaanad qaadaysaa u hoggaansanaanta ACID (Atomicity, Consistency, Isolation, Durability), taas oo hubinaysa in haddii uu shil dhaco, SQL Server uu dib u ciyaari karo (roll forward) ama uu ka noqon karo (roll back) macaamilka.

Faylasha Diiwaanka Virtual (VLFs) iyo Diiwaangelinta Wareegtada ah

Gudaha, faylka diiwaanka macaamilka ee jireed (LDF) wuxuu u qaybsan yahay qaybo yaryar oo macquul ah oo loo yaqaan Virtual Log Files (VLFs). Diiwaanka macaamilku wuxuu u shaqeeyaa si wareeg ah. Marka diiwaannada macaamilka la qoro, waxay buuxiyaan hal VLF oo waxay u guuraan kan xiga.

Marka diiwaanku gaaro dhammaadka faylka jireed, wuxuu isku dayaa inuu ku wareego bilowga. Si kastaba ha ahaatee, wuxuu qori karaa oo keliya VLF haddii VLF-taas loo calaamadeeyo inay tahay mid aan firfircoonayn. Haddii dhammaan VLFs ay firfircoon yihiin (macnaha waxay ka kooban yihiin diiwaanno macaamil oo SQL Server weli u baahan yahay), diiwaanku ma wareegi karo. Haddii koritaanka otomaatiga ah (auto-growth) uu shaqaynayo oo booska diskiga uu jiro, faylka jireed wuu korayaa. Haddii diskigu buuxo ama koritaanka otomaatiga ah uu xaddidan yahay, waxaad la kulmi doontaa Khaladka 9002.

Goynta Diiwaanka (Log Truncation) vs. Hoos u dhigista Diiwaanka (Log Shrinking)

Faham khaldan oo caadi ah ayaa ah in goynta diiwaanka ay yarayso cabbirka faylka jireed.
* Goynta Diiwaanka (Log Truncation): Habka loo calaamadeeyo VLFs-ka firfircoon inay noqdaan kuwo aan firfircoonayn, taas oo ka dhigaysa booska mid loo isticmaali karo dib. Tani ma yarayso cabbirka faylka LDF ee diskiga.
* Hoos u dhigista Diiwaanka (Log Shrinking): Habka loo yareeyo cabbirka faylka LDF si jireed loona celiyo booska nidaamka hawlgalka (OS).

Qaabka Soo-kabashada Buuxa (Full Recovery model), goynta diiwaanka keliya waxay dhacdaa marka kaydinta diiwaanka macaamilka (transaction log backup) si guul leh loo dhammaystiro (iyadoo loo malaynayo inaysan jirin habab kale oo diiwaanka ku haya mid firfircoon).

Ogaanshaha Khaladka “Diiwaanka Macaamilka oo Buuxa” (Khaladka 9002)

Marka diiwaanku buuxsamo, tallaabadaada ugu horreysa maaha inaad si indho la’aan ah ugu darto booska diskiga ama aad yarayso faylasha. Waa inaad aqoonsataa sababta diiwaanku uusan u go’i karin. SQL Server wuxuu bixiyaa hab lagu dhex dhisay si uu kuu sheego waxa ka horjoogsanaya dib-u-isticmaalka diiwaanka iyada oo loo marayo sys.databases catalog view.

Ku socodsii amarka T-SQL ee soo socda si aad u aqoonsato caqabadda:

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

Waxaad sidoo kale hubin kartaa isticmaalka booska hadda ee diiwaannada macaamilkaaga adoo isticmaalaya:

DBCC SQLPERF(LOGSPACE);

Xaaladaha caadiga ah ee log_reuse_wait_desc

  1. LOG_BACKUP: Database-ku wuxuu ku jiraa qaabka soo-kabashada Buuxa ama Bulk-Logged, oo kaydinta diiwaanka macaamilka lama qaadin dhawaan. Tani waa sababta ugu badan.
  2. ACTIVE_TRANSACTION: Macaamil muddo dheer socday (tusaale, dib-u-dhiska index-ka oo weyn ama macaamil aan la dhammaystirin oo la iloobay) ayaa diiwaanka ku haya mid firfircoon.
  3. REPLICATION / CDC: Transactional Replication ama Change Data Capture (CDC) waa shaqaynayaan, oo Log Reader Agent weli ma habayn macaamilka.
  4. AVAILABILITY_REPLICA: Kooxda AlwaysOn Availability, replica-ga labaad (secondary) waa la gooyay ama wuu is-waafajinayaa si tartiib ah, taas oo ku qasbaysa replica-ga koowaad (primary) inuu hayo diiwaannada macaamilka ilaa laga adkeeyo replica-ga labaad.

Hababka Soo-kabashada Degdegga ah: Xallinta Arrintan ee Wax-soo-saarka

Iyadoo ku xiran log_reuse_wait_desc ee la soo celiyo, jawaabtaada degdegga ah way kala duwanaan doontaa. Waa kuwan hababka soo-kabashada degdegga ah ee xaaladaha ugu badan.

Xaaladda 1: Kaydinta Diiwaanka oo maqan ama fashilmay (LOG_BACKUP)

Haddii nooca sugitaanku yahay LOG_BACKUP, xalku waa mid toos ah: waa inaad kaydisaa diiwaanka macaamilka.

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

Marka kaydintu dhammaato, VLFs-ka aan firfircoonayn waa la goyn doonaa, SQL Server-kuna wuxuu dib u bilaabi doonaa hawlihii caadiga ahaa. Haddii darawalkaaga kaydintu buuxo, waxaa laga yaabaa inaad u baahato inaad ku kaydiso wadaagga shabakadda (network share) ama aalad null ah (laguma talinayo ilaa database-ka si fudud loo soo saari karo, maadaama ay jebinayso silsiladda diiwaanka):

-- DIGNIIN: Tani waxay jebinaysaa silsiladda diiwaanka waxayna wax u dhimaysaa soo-kabashada waqtiga-gaar ah.
-- Kaliya isticmaal haddii ay gabi ahaanba lagama maarmaan tahay oo isla markiiba raaci kaydinta FULL.
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';

Xaaladda 2: Macaamil muddo dheer socday (ACTIVE_TRANSACTION)

Haddii hal macaamil uu socday saacado, waxay ka hortagtaa goynta diiwaanka muddadaas oo dhan. Marka hore, aqoonso macaamilka dhibka keenaya:

DBCC OPENTRAN('YourDatabaseName');

Amarkani wuxuu soo celinayaa macaamilka ugu da’da weyn ee firfircoon iyo aqoonsiga habka server-ka (SPID). Waxaad ururin kartaa faahfaahin dheeraad ah oo ku saabsan waxa SPID-ku samaynayo adoo waydiinaya aragtiyaha maamulka firfircoon (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>;

Haddii macaamilku yahay su’aal khaldan ama hab istaagay, waxaa laga yaabaa inaad u baahato inaad joojiso si aad u xorayso diiwaanka.

KILL <SPID>;

Fiiro gaar ah: Dilka macaamil weyn wuxuu kicin doonaa dib-u-celin (rollback), taas oo qaadan karta waqti badan oo si ku-meel-gaar ah u dhalin doonta hawl-qabad diiwaan oo dheeraad ah. Ha dib u bilaabin adeegga SQL Server inta lagu jiro dib-u-celinta, haddii kale database-ku wuxuu geli doonaa qaabka soo-kabashada marka la dib-u-bilaabo.

Xaaladda 3: Qoondeynta Booska Degdegga ah (Disk-gu waa 100% buuxa)

Haddii faylka LDF uu isticmaalay dhammaan darawalka, xitaa ma socodsiin kartid kaydin sababtoo ah SQL Server wuxuu u baahan yahay boos yar oo diiwaan ah si uu u duubo dhacdada kaydinta lafteeda. Xaaladdan, waa inaad ku dartaa fayl diiwaan labaad darawal kale oo leh boos la heli karo.

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

Tani waxay isla markiiba siinaysaa SQL Server meel uu ku neefsado. Marka database-ku online noqdo, qaado kaydinta diiwaanka macaamilka, faaruqi faylka diiwaanka labaad, oo ka saar:

-- 1. Qaado kaydinta diiwaanka si aad u goyso diiwaanka
BACKUP LOG [YourDatabaseName] TO DISK = '...';

-- 2. Faaruqi faylka diiwaanka ku-meel-gaarka ah
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);

-- 3. Ka saar faylka diiwaanka ku-meel-gaarka ah
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];

Hababka Ugu Wanaagsan ee Ka-hortagga iyo Maamulka Diiwaanka Macaamilka

Xallinta dhibaatooyinka ka dib markay dhacaan waa mid walaac leh waxayna saameyn ku yeelataa SLAs. Hirgelinta hababka ugu wanaagsan ee qaab-dhismeedka iyo hawlgalka waa lagama maarmaan u ah xasilloonida database-ka ganacsiga.

1. Hirgelinta Istaraatiijiyad Kaydin oo Adag oo Otomaatig ah

Haddii database-ku ku jiro qaabka soo-kabashada Buuxa, kaydinta diiwaanka macaamilka ee joogtada ah waa qasab. Iyadoo ku xiran Ujeeddada Barta Soo-kabashada (RPO) iyo mugga macaamilka, kaydinta diiwaanka waa inay dhacdaa 5 ilaa 15 daqiiqo kasta.

Xalalka kaydinta ganacsiga sida CloudSave ayaa habkan si weyn u fududeeya. Adoo si toos ah ula midoobaya SQL Server iyada oo loo marayo VDI (Virtual Device Interface), CloudSave waxay u oggolaanaysaa DBAs inay habeeyaan kaydinta diiwaanka macaamilka ee ku salaysan siyaasadda iyo soo noqnoqoshada sare. Tani waxay hubinaysaa in diiwaannada si joogto ah loo gooyo, si ammaan ah loo siriyo, loona kaydiyo meel ka baxsan goobta ama kaydinta daruuriga ah ee aan la beddeli karin, taas oo ka hortagaysa xaaladda sugitaanka LOG_BACKUP iyada oo aan loo baahnayn shaqooyin SQL Agent oo adag oo caado ah.

2. Cabbirka Saxda ah ee Diiwaanka Macaamilka iyo Maamulka VLFs

Ku tiirsanaanta koritaanka otomaatiga ah si loo maamulo cabbirka diiwaanka macaamilkaaga waa hab khatar ah. Hawlaha koritaanka otomaatiga ah waa qaali waxayna hakad geliyaan habaynta macaamilka inta diskiga la eber-bilowayo (haddii aan la shaqaysiin Instant File Initialization, taas oo aan khusayn faylasha diiwaanka).

Intaa waxaa dheer, koritaanka otomaatiga ah ee yar oo soo noqnoqda (tusaale, koritaanka 10% ama 50MB mar kasta) waxay horseedaa kala-go’a VLF. Diiwaanka macaamilka oo leh kumanaan VLFs oo yaryar ayaa si weyn u hoos u dhigi doona waqtiyada bilowga database-ka, waxqabadka kaydinta, iyo daahitaanka dib-u-celinta.

  • Pre-size diiwaanka: Falanqee hawlahaaga dayactirka ee ugu waaweyn (sida dib-u-dhiska index-ka) oo horay u sii cabbir faylka LDF si aad u dejiso iyada oo aan la korin.
  • Deji koritaanka otomaatiga ah ee go’an: Ka beddel koritaanka otomaatiga ah boqolkiiba una beddel cabbir go’an (tusaale, 1GB ama 5GB) si loo hubiyo in VLFs lagu abuuro cabbir caafimaad qaba.

Waxaad hubin kartaa tirada VLF-gaaga adoo isticmaalaya su’aashan soo socota (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'));

Haddii tirada VLF-gaagu ay ka badan tahay 500, tixgeli inaad sugto muddo deggan, yarayso diiwaanka ilaa cabbirka ugu yar, oo gacanta ku koriso ilaa cabbirka loo baahan yahay qaybo waaweyn.

3. Hagaajinta Hawlaha Dayactirka Index-ka

Dib-u-dhiska index-ka waa hawlo si buuxda loo diiwaangeliyay, xitaa qaabka soo-kabashada Bulk-Logged (iyadoo ku xiran nooca index-ka). Dib-u-dhiska index 500GB ah wuxuu dhalin doonaa ugu yaraan 500GB oo diiwaanno macaamil ah.

Si loo yareeyo buuxdhaafka diiwaanka inta lagu jiro dayactirka:
* Isticmaal SORT_IN_TEMPDB = ON marka aad dib u dhisayso index-yada. Tani waxay u wareejinaysaa marxaladda kala-soocidda TempDB, taas oo yaraynaysa culayska diiwaanka macaamilka ee database-ka isticmaalaha.
* Ka beddel dib-u-dhiska index-ka una beddel dib-u-habaynta index-ka (reorganize) halka ay suurtogal tahay, maadaama dib-u-habayntu ay tahay mid diiwaan-waxtar leh oo la joojin karo iyada oo aan dib loo celin hawsha oo dhan.
* Batch hawlaha waaweyn ee DELETE ama UPDATE. Halkii aad tirtiri lahayd 10 milyan oo saf hal macaamil, ku tirtir qaybo ka mid ah 50,000, adoo dhammaystiraya oo u oggolaanaya kaydinta diiwaanka inay gooyaan diiwaanka inta u dhaxaysa qaybaha.

4. La socodka Helitaanka Sare iyo Topologies-ka Dib-u-celinta

Kooxda AlwaysOn Availability, replica-ga koowaad ma goyn karo diiwaankiisa ilaa diiwaannada macaamilka laga adkeeyo dhammaan replica-yada labaad ee is-waafajinta (synchronous) iyo kuwa aan is-waafajinayn (asynchronous).

Haddii replica-ga labaad uu khadka ka baxo, ama haddii xawaaraha shabakadu uusan la socon karin heerka dhalashada macaamilka ee koowaad, safka dirista ee koowaad wuu kori doonaa, diiwaankuna wuu buuxsami doonaa (nooca sugitaanka AVAILABILITY_REPLICA).

Hirgeli la socod adag oo loogu talagalay SQLServer:Replica > Log Send Queue counter-ka waxqabadka. Haddii replica-ga labaad uu si joogto ah u lumo, waa inaad ka saartaa Kooxda Helitaanka ama hakisaa dhaqdhaqaaqa xogta si aad u oggolaato in diiwaanka koowaad uu go’o.

Gabagabo

La kulanka diiwaanka macaamilka oo buuxa waa caqabad u baahan in maamulayaasha database-ku ay maraan, laakiin uma baahna inay keento wakhti dheer oo shaqo la’aan ah. Adoo fahmaya makaanikada Qorista-Hore ee Diiwaanka iyo VLFs, waxaad si dhakhso ah u ogaan kartaa sababta asaasiga ah adoo isticmaalaya sys.databases oo aad codsan kartaa istaraatiijiyadda soo-kabashada degdegga ah ee saxda ah.

Xasilloonida muddada dheer waxay ku tiirsan tahay ka guurista hagaajinta falcelinta. Horay u sii cabbirka faylasha diiwaankaaga, hagaajinta hababka dayactirka, iyo isticmaalka aaladaha kaydinta ee heerka ganacsi sida CloudSave si loo fuliyo jadwalada kaydinta diiwaanka ee adag oo otomaatig ah waxay hubin doontaa in diiwaannada macaamilkaagu ay ahaadaan kuwo caafimaad qaba, go’an, oo diyaar u ah inay taageeraan hawlaha wax-soo-saarka ee mugga sare leh.

Categories