Log ya Muamala ya MSSQL Imejaa: Mikakati ya Kuzuia na Kurejesha Haraka

Kwa Wasimamizi wa Hifadhidata (DBAs) na wahandisi wa DevOps wanaosimamia Microsoft SQL Server, ni arifa chache zinazosababisha wasiwasi wa haraka kama Hitilafu 9002: The transaction log for database ‘X’ is full (Log ya miamala ya hifadhidata ‘X’ imejaa). Wakati log ya miamala inapojaa na kushindwa kukua, hifadhidata inakuwa ya kusoma tu (read-only). Operesheni zote za INSERT, UPDATE, na DELETE husimama, miamala ya programu inafeli, na uzalishaji husimama kabisa.

Kuelewa usanifu wa msingi wa log ya miamala ya SQL Server, kutambua kwa usahihi chanzo cha tatizo, na kutekeleza taratibu za urejeshaji wa haraka ni ujuzi muhimu kwa ajili ya kudumisha upatikanaji wa juu wa huduma. Mwongozo huu wa kina unachunguza jinsi log ya miamala inavyofanya kazi, jinsi ya kutatua log iliyojaa wakati wa dharura, na mbinu bora za usanifu ili kuzuia tatizo hili lisitokee tena.

Kuelewa Usanifu wa Log ya Miamala ya SQL Server

Ili kutatua tatizo la log ya miamala iliyojaa kwa ufanisi, lazima kwanza uelewe jinsi SQL Server inavyoandika na kusimamia data.

Write-Ahead Logging (WAL)

SQL Server inatumia itifaki ya Write-Ahead Logging (WAL). Kila wakati mabadiliko ya data yanapotokea, mabadiliko hayo kwanza huandikwa kwenye log ya miamala iliyo kwenye kumbukumbu (memory), kisha huhamishiwa kwenye faili ya log ya kimwili iliyo kwenye diski kabla ya kurasa halisi za data kusasishwa kwenye faili za hifadhidata (MDF/NDF). Hii inahakikisha utiifu wa ACID (Atomicity, Consistency, Isolation, Durability), ikihakikisha kwamba katika tukio la ajali, SQL Server inaweza kurudia (roll forward) au kutengua (roll back) miamala.

Virtual Log Files (VLFs) na Circular Logging

Kwa ndani, faili ya log ya miamala ya kimwili (LDF) imegawanywa katika sehemu ndogo, za kimantiki zinazoitwa Virtual Log Files (VLFs). Log ya miamala inafanya kazi kwa mzunguko. Rekodi za log zinapoandikwa, zinajaza VLF moja na kuhamia inayofuata.

Wakati log inapofika mwisho wa faili ya kimwili, inajaribu kuanza tena kutoka mwanzo. Hata hivyo, inaweza kuandika juu ya VLF tu ikiwa VLF hiyo imewekwa alama kama isiyo hai (inactive). Ikiwa VLFs zote ni hai (ikimaanisha zina rekodi za log ambazo bado zinahitajika na SQL Server), log haiwezi kuanza tena. Ikiwa auto-growth imewashwa na nafasi ya diski inapatikana, faili ya kimwili inakua. Ikiwa diski imejaa au auto-growth imezuiwa, utakutana na Hitilafu 9002.

Log Truncation dhidi ya Log Shrinking

Dhana potofu ya kawaida ni kwamba kufuta (truncate) log kunapunguza ukubwa wa faili ya kimwili.
* Log Truncation: Mchakato wa kuweka alama kwenye VLFs hai kama zisizo hai, na kufanya nafasi hiyo ipatikane kwa matumizi tena. Haikupunguza ukubwa wa faili ya LDF kwenye diski.
* Log Shrinking: Mchakato wa kupunguza ukubwa wa faili ya LDF kimwili na kurudisha nafasi kwenye mfumo wa uendeshaji.

Katika mfumo wa Full Recovery, log truncation hutokea tu wakati backup ya log ya miamala imekamilika kwa mafanikio (ikizingatiwa kuwa hakuna michakato mingine inayoshikilia log hiyo kuwa hai).

Kutambua Hitilafu ya “Transaction Log Full” (Hitilafu 9002)

Wakati log imejaa, hatua yako ya kwanza si kuongeza nafasi ya diski au kupunguza faili kiholela. Lazima utambue kwa nini log haiwezi kufutwa (truncate). SQL Server inatoa utaratibu wa ndani wa kukuambia hasa nini kinazuia matumizi ya log kupitia mwonekano wa katalogi wa sys.databases.

Endesha amri ifuatayo ya T-SQL ili kutambua kikwazo:

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

Unaweza pia kuangalia matumizi ya sasa ya nafasi ya log zako za miamala kwa kutumia:

DBCC SQLPERF(LOGSPACE);

Hali za kawaida za log_reuse_wait_desc

1. LOG_BACKUP: Hifadhidata iko katika mfumo wa Full au Bulk-Logged recovery, na backup ya log ya miamala haijachukuliwa hivi karibuni. Hii ndiyo sababu ya kawaida zaidi.
2. ACTIVE_TRANSACTION: Muamala unaoendelea kwa muda mrefu (kwa mfano, ujenzi mkubwa wa index au muamala uliosahaulika ambao haujakamilishwa) unaifanya log kuwa hai.
3. REPLICATION / CDC: Transactional Replication au Change Data Capture (CDC) imewashwa, na Log Reader Agent bado haijachakata miamala hiyo.
4. AVAILABILITY_REPLICA: Katika AlwaysOn Availability Group, replica ya pili imekatika au inasawazisha polepole sana, ikilazimisha replica ya msingi kuhifadhi rekodi za log hadi ziimarishwe kwenye replica ya pili.

Mikakati ya Urejeshaji wa Haraka: Kutatua Tatizo katika Uzalishaji

Kulingana na log_reuse_wait_desc iliyorejeshwa, mwitikio wako wa dharura utatofautiana. Hizi hapa ni mikakati ya urejeshaji wa haraka kwa hali za kawaida.

Hali ya 1: Backup za Log Zilizokosekana au Zilizofeli (LOG_BACKUP)

Ikiwa aina ya kusubiri ni LOG_BACKUP, suluhisho ni rahisi: lazima ufanye backup ya log ya miamala.

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

Mara tu backup inapokamilika, VLFs zisizo hai zitafutwa, na SQL Server itaanza tena shughuli za kawaida. Ikiwa diski yako ya backup imejaa, unaweza kuhitaji kufanya backup kwenye mtandao wa muda au kifaa cha null (haipendekezwi isipokuwa hifadhidata inaweza kuzalishwa upya kwa urahisi, kwani inavunja mnyororo wa log):

-- ONYO: Hii inavunja mnyororo wa log na kuhatarisha urejeshaji wa wakati uliopita.
-- Tumia tu ikiwa ni lazima kabisa na ufuate mara moja na backup ya FULL.
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';

Hali ya 2: Miamala Inayoendelea kwa Muda Mrefu (ACTIVE_TRANSACTION)

Ikiwa muamala mmoja umekuwa ukiendelea kwa saa nyingi, unazuia log truncation kwa muda wote huo. Kwanza, tambua muamala unaosababisha tatizo:

DBCC OPENTRAN('YourDatabaseName');

Amri hii inarejesha muamala kongwe zaidi unaoendelea na Kitambulisho chake cha Mchakato wa Seva (SPID). Unaweza kukusanya maelezo zaidi kuhusu kile SPID inachofanya kwa kuuliza 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>;

Ikiwa muamala huo ni swali potovu au mchakato uliokwama, unaweza kuhitaji kuusitisha ili kuachia log.

KILL <SPID>;

Kumbuka: Kusitisha muamala mkubwa kutasababisha rollback, ambayo inaweza kuchukua muda mwingi na itazalisha shughuli za ziada za log kwa muda. Usianzishe upya huduma ya SQL Server wakati wa rollback, au hifadhidata itaingia katika hali ya urejeshaji (recovery mode) baada ya kuanza upya.

Hali ya 3: Ugawaji wa Nafasi ya Dharura (Diski Imejaa 100%)

Ikiwa faili ya LDF imetumia diski nzima, huwezi hata kufanya backup kwa sababu SQL Server inahitaji kiasi kidogo cha nafasi ya log ili kurekodi tukio la backup yenyewe. Katika hali hii, lazima uongeze faili ya pili ya log kwenye diski nyingine yenye nafasi inayopatikana.

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

Hii inatoa SQL Server nafasi ya kupumua mara moja. Mara tu hifadhidata inapokuwa mtandaoni, fanya backup ya log ya miamala, futa faili ya pili ya log, na uiondoe:

-- 1. Fanya backup ya log ili kufuta log
BACKUP LOG [YourDatabaseName] TO DISK = '...';

-- 2. Futa faili ya log ya muda
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);

-- 3. Ondoa faili ya log ya muda
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];

Mbinu Bora za Kuzuia na Kusimamia Log ya Miamala

Utatuzi wa matatizo baada ya kutokea ni wa kusumbua na unaathiri SLAs. Utekelezaji wa mbinu bora za usanifu na uendeshaji ni muhimu kwa utulivu wa hifadhidata ya biashara.

1. Tekeleza Mkakati wa Backup Imara na Otomatiki

Ikiwa hifadhidata iko katika mfumo wa Full recovery, backup za mara kwa mara za log ya miamala ni lazima. Kulingana na Lengo lako la Pointi ya Urejeshaji (RPO) na kiasi cha miamala, backup za log zinapaswa kufanyika kila baada ya dakika 5 hadi 15.

Suluhisho za backup za biashara kama CloudSave hurahisisha mchakato huu kwa kiasi kikubwa. Kwa kuunganishwa moja kwa moja na SQL Server kupitia VDI (Virtual Device Interface), CloudSave inaruhusu DBAs kusanidi backup za log za miamala za mara kwa mara zinazoendeshwa na sera. Hii inahakikisha log zinafutwa mara kwa mara, zimesimbwa kwa usalama, na kuhifadhiwa nje ya tovuti au kwenye hifadhi ya wingu isiyoweza kubadilika, kuzuia hali ya kusubiri ya LOG_BACKUP bila kuhitaji kazi ngumu za SQL Agent zilizobinafsishwa.

2. Rekebisha Ukubwa wa Log ya Miamala na Simamia VLFs

Kutegemea auto-growth kusimamia ukubwa wa log yako ya miamala ni mazoea hatari. Operesheni za auto-growth ni ghali na husitisha usindikaji wa miamala wakati diski inapoanzishwa kwa sifuri (isipokuwa Instant File Initialization imewashwa, ambayo haitumiki kwa faili za log).

Zaidi ya hayo, auto-growth ndogo na za mara kwa mara (kwa mfano, kukua kwa 10% au 50MB kwa wakati mmoja) husababisha VLF fragmentation. Log ya miamala yenye maelfu ya VLFs ndogo itapunguza sana kasi ya kuanza kwa hifadhidata, utendaji wa backup, na latency ya replication.

• Weka ukubwa wa awali wa log: Changanua operesheni zako kubwa za matengenezo (kama ujenzi wa index) na uweke ukubwa wa awali wa faili ya LDF ili kuikidhi bila kukua.
• Weka auto-growth isiyobadilika: Badilisha auto-growth kutoka asilimia hadi ukubwa usiobadilika (kwa mfano, 1GB au 5GB) ili kuhakikisha VLFs zinaundwa kwa ukubwa unaofaa.

Unaweza kuangalia idadi yako ya VLF kwa kutumia swali lifuatalo (kwa 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'));

Ikiwa idadi yako ya VLF ni zaidi ya 500, fikiria kusubiri kipindi cha utulivu, kupunguza log hadi ukubwa mdogo, na kuikuza kwa mikono kurudi kwenye ukubwa wake unaohitajika katika sehemu kubwa.

3. Boresha Operesheni za Matengenezo ya Index

Ujenzi wa index ni operesheni zinazorekodiwa kikamilifu, hata katika mfumo wa Bulk-Logged recovery (kulingana na aina ya index). Kujenga upya index ya 500GB kutazalisha angalau 500GB ya rekodi za log ya miamala.

Ili kupunguza uvimbe wa log wakati wa matengenezo:
* Tumia SORT_IN_TEMPDB = ON wakati wa kujenga upya index. Hii huhamishia awamu ya kupanga kwenye TempDB, ikipunguza mzigo kwenye log ya miamala ya hifadhidata ya mtumiaji.
* Badilisha kutoka kujenga upya index hadi kupanga upya (reorganize) index inapowezekana, kwani kupanga upya ni bora zaidi kwa log na kunaweza kusitishwa bila kutengua operesheni nzima.
* Fanya operesheni kubwa za DELETE au UPDATE kwa makundi. Badala ya kufuta safu milioni 10 katika muamala mmoja, zifute katika makundi ya 50,000, ukikamilisha na kuruhusu backup za log kufuta log kati ya makundi.

4. Fuatilia Upatikanaji wa Juu na Topolojia za Replication

Katika AlwaysOn Availability Groups, replica ya msingi haiwezi kufuta log yake hadi rekodi za log ziimarishwe kwenye replica zote za pili za synchronous na asynchronous.

Ikiwa replica ya pili inatoka nje ya mtandao, au ikiwa bandwidth ya mtandao haiwezi kwenda sambamba na kasi ya uzalishaji wa miamala ya msingi, foleni ya kutuma ya msingi itakua, na log itajaa (aina ya kusubiri ya AVAILABILITY_REPLICA).

Tekeleza ufuatiliaji imara kwa kaunta ya utendaji ya SQLServer:Replica > Log Send Queue. Ikiwa replica ya pili imepotea kabisa, lazima uiondoe kwenye Availability Group au usimamishe usafirishaji wa data ili kuruhusu log ya msingi ifutwe.

Hitimisho

Kukutana na log ya miamala iliyojaa ni changamoto ya kawaida kwa wasimamizi wa hifadhidata, lakini haifai kusababisha muda mrefu wa kutofanya kazi. Kwa kuelewa mechanics ya Write-Ahead Logging na VLFs, unaweza kutambua haraka chanzo cha tatizo kwa kutumia sys.databases na kutumia mkakati sahihi wa urejeshaji wa haraka.

Utulivu wa muda mrefu unategemea kuacha marekebisho ya dharura. Kuweka ukubwa wa awali wa faili zako za log, kuboresha taratibu za matengenezo, na kutumia majukwaa ya backup ya kiwango cha biashara kama CloudSave kutekeleza ratiba kali na otomatiki za backup za log kutahakikisha log zako za miamala zinabaki na afya, zimefutwa, na ziko tayari kusaidia mizigo ya kazi ya uzalishaji yenye kasi kubwa.