MSSQL Tranzaksiya Jurnalı Doludur: Qarşısının Alınması və Sürətli Bərpa Strategiyaları

Məlumat bazası administratorları (DBA) və Microsoft SQL Server-i idarə edən DevOps mühəndisləri üçün 9002 nömrəli xəta qədər dərhal narahatlıq doğuran çox az xəbərdarlıq var: ‘X’ verilənlər bazası üçün tranzaksiya jurnalı doludur. Tranzaksiya jurnalı dolduqda və böyüyə bilmədikdə, verilənlər bazası effektiv şəkildə yalnız oxuna bilən (read-only) rejimə keçir. Bütün INSERT, UPDATE və DELETE əməliyyatları dayanır, tətbiq tranzaksiyaları uğursuz olur və istehsal prosesi tamamilə durur.

SQL Server tranzaksiya jurnalının əsas arxitekturasını başa düşmək, kök səbəbi dəqiq diaqnoz etmək və sürətli bərpa prosedurlarını yerinə yetirmək yüksək əlçatanlığı qorumaq üçün kritik bacarıqlardır. Bu əhatəli bələdçi tranzaksiya jurnalının mexanikasını, fövqəladə vəziyyətdə dolu jurnalı necə həll edəcəyinizi və bunun yenidən baş verməsinin qarşısını almaq üçün arxitektura üzrə ən yaxşı təcrübələri araşdırır.

SQL Server Tranzaksiya Jurnalı Arxitekturasını Anlamaq

Dolu tranzaksiya jurnalında nasazlıqları effektiv şəkildə aradan qaldırmaq üçün əvvəlcə SQL Server-in məlumatları necə yazdığını və idarə etdiyini başa düşməlisiniz.

Write-Ahead Logging (WAL)

SQL Server “Write-Ahead Logging” (WAL) protokolundan istifadə edir. Məlumat dəyişikliyi baş verdikdə, dəyişiklik əvvəlcə yaddaşdakı tranzaksiya jurnalına yazılır, sonra verilənlər bazası fayllarındakı (MDF/NDF) faktiki məlumat səhifələri yenilənməzdən əvvəl diskdəki fiziki jurnal faylına köçürülür. Bu, ACID (Atomiklik, Ardıcıllıq, İzolyasiya, Davamlılıq) uyğunluğunu təmin edir və qəza baş verdikdə SQL Server-in tranzaksiyaları yenidən oynada (roll forward) və ya geri qaytara (roll back) biləcəyinə zəmanət verir.

Virtual Jurnal Faylları (VLF) və Dairəvi Jurnallama

Daxili olaraq, fiziki tranzaksiya jurnalı faylı (LDF) Virtual Jurnal Faylları (VLF) adlanan daha kiçik, məntiqi seqmentlərə bölünür. Tranzaksiya jurnalı dairəvi şəkildə işləyir. Jurnal qeydləri yazıldıqca, onlar bir VLF-ni doldurur və növbətiyə keçir.

Jurnal fiziki faylın sonuna çatdıqda, əvvələ qayıtmağa çalışır. Lakin o, yalnız bir VLF aktiv deyil kimi işarələnibsə, onu əvəz edə bilər. Əgər bütün VLF-lər aktivdirsə (yəni SQL Server tərəfindən hələ də tələb olunan jurnal qeydlərini ehtiva edirsə), jurnal dairəvi şəkildə davam edə bilmir. Əgər avtomatik böyümə (auto-growth) aktivdirsə və disk sahəsi mövcuddursa, fiziki fayl böyüyür. Disk doludursa və ya avtomatik böyümə məhduddursa, 9002 nömrəli xəta ilə qarşılaşırsınız.

Jurnalın Kəsilməsi (Truncation) və Kiçildilməsi (Shrinking)

Ümumi bir yanlış fikir budur ki, jurnalın kəsilməsi fiziki faylın ölçüsünü azaldır.
* Jurnalın Kəsilməsi (Log Truncation): Aktiv VLF-lərin aktiv olmayan kimi işarələnməsi prosesidir, bu da sahəni təkrar istifadə üçün əlçatan edir. Bu, diskdəki LDF faylının ölçüsünü azaltmır.
* Jurnalın Kiçildilməsi (Log Shrinking): LDF faylının ölçüsünün fiziki olaraq azaldılması və sahənin əməliyyat sisteminə qaytarılması prosesidir.

Tam Bərpa (Full Recovery) modelində, jurnalın kəsilməsi yalnız tranzaksiya jurnalı ehtiyat nüsxəsi uğurla tamamlandıqda baş verir (əgər başqa proseslər jurnalı aktiv saxlamırsa).

“Tranzaksiya Jurnalı Doludur” Xətasının (Xəta 9002) Diaqnozu

Jurnal dolduqda, ilk addımınız kor-koranə disk sahəsi əlavə etmək və ya faylları kiçiltmək olmamalıdır. Jurnalın niyə kəsilə bilmədiyini müəyyən etməlisiniz. SQL Server sys.databases kataloq görünüşü vasitəsilə jurnalın təkrar istifadəsini nəyin əngəllədiyini sizə dəqiq söyləmək üçün daxili mexanizm təqdim edir.

Dar boğazı müəyyən etmək üçün aşağıdakı T-SQL əmrini işə salın:

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

Siz həmçinin aşağıdakı əmrlə tranzaksiya jurnallarınızın cari sahə istifadəsini yoxlaya bilərsiniz:

DBCC SQLPERF(LOGSPACE);

Ümumi log_reuse_wait_desc Vəziyyətləri

1. LOG_BACKUP: Verilənlər bazası Tam və ya Toplu-Jurnallanmış (Bulk-Logged) bərpa modelindədir və tranzaksiya jurnalı ehtiyat nüsxəsi bu yaxınlarda götürülməyib. Bu, ən çox rast gəlinən səbəbdir.
2. ACTIVE_TRANSACTION: Uzun müddət davam edən tranzaksiya (məsələn, nəhəng indeks yenidən qurulması və ya unudulmuş təsdiqlənməmiş tranzaksiya) jurnalı aktiv saxlayır.
3. REPLICATION / CDC: Tranzaksiya Replikasiyası və ya Dəyişiklik Məlumatlarının Tutulması (CDC) aktivdir və Jurnal Oxuyucu Agent (Log Reader Agent) hələ tranzaksiyaları emal etməyib.
4. AVAILABILITY_REPLICA: AlwaysOn Əlçatanlıq Qrupunda (Availability Group), ikinci dərəcəli replika əlaqəni kəsib və ya çox yavaş sinxronizasiya edir, bu da əsas replikanı jurnal qeydlərini ikinci dərəcəli replikada bərkidilənə qədər saxlamağa məcbur edir.

Sürətli Bərpa Strategiyaları: İstehsalda Problemin Həlli

Qaytarılan log_reuse_wait_desc-dən asılı olaraq, fövqəladə cavabınız dəyişəcək. Budur ən çox rast gəlinən ssenarilər üçün sürətli bərpa strategiyaları.

Ssenari 1: İtmiş və ya Uğursuz Jurnal Ehtiyat Nüsxələri (LOG_BACKUP)

Əgər gözləmə növü LOG_BACKUP-dırsa, həll yolu sadədir: tranzaksiya jurnalının ehtiyat nüsxəsini çıxarmalısınız.

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

Ehtiyat nüsxə tamamlandıqdan sonra, aktiv olmayan VLF-lər kəsiləcək və SQL Server normal əməliyyatları bərpa edəcək. Əgər ehtiyat nüsxə diskiniz doludursa, müvəqqəti şəbəkə paylaşımına və ya boş cihaza (null device) ehtiyat nüsxə çıxarmalı ola bilərsiniz (verilənlər bazası asanlıqla bərpa oluna bilmirsə, bu tövsiyə edilmir, çünki bu, jurnal zəncirini qırır):

-- XƏBƏRDARLIQ: Bu, jurnal zəncirini qırır və müəyyən bir ana qayıtma (point-in-time) bərpasını təhlükəyə atır.
-- Yalnız mütləq lazım olduqda istifadə edin və dərhal tam ehtiyat nüsxə (FULL backup) ilə davam edin.
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';

Ssenari 2: Uzun Müddət Davam edən Aktiv Tranzaksiyalar (ACTIVE_TRANSACTION)

Əgər bir tranzaksiya saatlarla davam edirsə, bu, bütün müddət ərzində jurnalın kəsilməsinə mane olur. Əvvəlcə problemi yaradan tranzaksiyanı müəyyən edin:

DBCC OPENTRAN('YourDatabaseName');

Bu əmr ən köhnə aktiv tranzaksiyanı və onun Server Proses ID-sini (SPID) qaytarır. Dinamik idarəetmə görünüşlərini (DMV) sorğulamaqla SPID-in nə etdiyi barədə daha çox məlumat əldə edə bilərsiniz:

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>;

Əgər tranzaksiya lazımsız sorğu və ya dayanmış prosesdirsə, jurnalı azad etmək üçün onu dayandırmalı ola bilərsiniz.

KILL <SPID>;

Qeyd: Nəhəng tranzaksiyanı dayandırmaq geri qaytarma (rollback) prosesini işə salacaq, bu da xeyli vaxt apara bilər və müvəqqəti olaraq əlavə jurnal aktivliyi yaradacaq. Geri qaytarma zamanı SQL Server xidmətini yenidən başlatmayın, əks halda verilənlər bazası yenidən başladıqda bərpa rejiminə keçəcək.

Ssenari 3: Fövqəladə Sahə Ayırılması (Disk 100% Doludur)

Əgər LDF faylı bütün diski tutubsa, siz hətta ehtiyat nüsxə də çıxara bilməzsiniz, çünki SQL Server ehtiyat nüsxə hadisəsinin özünü qeyd etmək üçün bir az jurnal sahəsinə ehtiyac duyur. Bu ssenaridə, mövcud sahəsi olan başqa bir diskdə ikinci dərəcəli jurnal faylı əlavə etməlisiniz.

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

Bu, dərhal SQL Server-ə nəfəs almaq üçün yer verir. Verilənlər bazası onlayn olduqdan sonra tranzaksiya jurnalı ehtiyat nüsxəsini çıxarın, ikinci dərəcəli jurnal faylını boşaldın və onu silin:

-- 1. Jurnalı kəsmək üçün jurnal ehtiyat nüsxəsi çıxarın
BACKUP LOG [YourDatabaseName] TO DISK = '...';

-- 2. Müvəqqəti jurnal faylını boşaldın
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);

-- 3. Müvəqqəti jurnal faylını silin
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];

Tranzaksiya Jurnalının Qarşısının Alınması və İdarə Edilməsi üçün Ən Yaxşı Təcrübələr

Reaktiv nasazlıqların aradan qaldırılması streslidir və SLA-lara təsir edir. Proaktiv arxitektura və əməliyyat təcrübələrinin tətbiqi müəssisə verilənlər bazasının sabitliyi üçün vacibdir.

1. Güclü, Avtomatlaşdırılmış Ehtiyat Nüsxə Strategiyası tətbiq edin

Əgər verilənlər bazası Tam bərpa modelindədirsə, tez-tez tranzaksiya jurnalı ehtiyat nüsxələrinin çıxarılması məcburidir. Bərpa Nöqtəsi Məqsədinizdən (RPO) və tranzaksiya həcmindən asılı olaraq, jurnal ehtiyat nüsxələri hər 5-15 dəqiqədən bir baş verməlidir.

CloudSave kimi müəssisə ehtiyat nüsxə həlləri bu prosesi əhəmiyyətli dərəcədə sadələşdirir. VDI (Virtual Cihaz İnterfeysi) vasitəsilə birbaşa SQL Server ilə inteqrasiya edərək, CloudSave DBA-lara siyasətə əsaslanan, yüksək tezlikli tranzaksiya jurnalı ehtiyat nüsxələrini konfiqurasiya etməyə imkan verir. Bu, jurnalların davamlı olaraq kəsilməsini, təhlükəsiz şəkildə şifrələnməsini və kənar və ya dəyişməz bulud yaddaşında saxlanmasını təmin edir, mürəkkəb xüsusi SQL Agent işlərinə ehtiyac duymadan LOG_BACKUP gözləmə vəziyyətinin qarşısını alır.

2. Tranzaksiya Jurnalını Düzgün Ölçün və VLF-ləri İdarə Edin

Tranzaksiya jurnalınızın ölçüsünü idarə etmək üçün avtomatik böyüməyə (auto-growth) güvənmək təhlükəli bir anti-pattern-dir. Avtomatik böyümə əməliyyatları bahalıdır və disk sıfırla ilkinləşdirilənə qədər tranzaksiya emalını dayandırır (əgər Instant File Initialization aktiv deyilsə, bu jurnal fayllarına aid deyil).

Bundan əlavə, tez-tez baş verən kiçik avtomatik böyümələr (məsələn, hər dəfə 10% və ya 50MB böyümək) VLF fraqmentasiyasına gətirib çıxarır. Minlərlə kiçik VLF-ə malik tranzaksiya jurnalı verilənlər bazasının işə salınma vaxtlarını, ehtiyat nüsxə performansını və replikasiya gecikməsini ciddi şəkildə pisləşdirəcək.

• Jurnalı əvvəlcədən ölçün: Ən böyük texniki xidmət əməliyyatlarınızı (indeks yenidən qurulması kimi) təhlil edin və LDF faylını böyümədən onları yerinə yetirmək üçün əvvəlcədən ölçün.
• Sabit avtomatik böyümə təyin edin: VLF-lərin sağlam ölçüdə yaradılmasını təmin etmək üçün avtomatik böyüməni faizdən sabit ölçüyə (məsələn, 1GB və ya 5GB) dəyişin.

Siz VLF sayınızı aşağıdakı sorğu ilə yoxlaya bilərsiniz (SQL Server 2017+ üçün):

SELECT 
    db_name(database_id) AS DatabaseName,
    COUNT(vlf_sequence_number) AS VLF_Count
FROM sys.dm_db_log_info(DB_ID('YourDatabaseName'));

Əgər VLF sayınız 500-dən çoxdursa, sakit bir dövrü gözləməyi, jurnalı minimal ölçüyə qədər kiçiltməyi və onu əl ilə böyük hissələrlə tələb olunan ölçüsünə qaytarmağı düşünün.

3. İndeks Texniki Xidmət Əməliyyatlarını Optimallaşdırın

İndeks yenidən qurulması (rebuilds), hətta Toplu-Jurnallanmış bərpa modelində belə (indeks növündən asılı olaraq) tam jurnallanan əməliyyatlardır. 500GB-lıq indeksi yenidən qurmaq ən azı 500GB tranzaksiya jurnalı qeydi yaradacaq.

Texniki xidmət zamanı jurnalın şişməsinin qarşısını almaq üçün:
* İndeksləri yenidən qurarkən SORT_IN_TEMPDB = ON istifadə edin. Bu, sıralama mərhələsini TempDB-yə yükləyir və istifadəçi verilənlər bazasının tranzaksiya jurnalındakı yükü azaldır.
* Mümkün olduqda indeks yenidən qurulmasından (rebuilds) indeks yenidən təşkilinə (reorganizes) keçin, çünki yenidən təşkil etmələr jurnal baxımından daha səmərəlidir və bütün əməliyyatı geri qaytarmadan dayandırıla bilər.
* Böyük DELETE və ya UPDATE əməliyyatlarını partiyalara bölün. 10 milyon sətri bir tranzaksiyada silmək əvəzinə, onları 50,000-lik hissələrlə silin, təsdiqləyin və jurnal ehtiyat nüsxələrinin partiyalar arasında jurnalı kəsməsinə icazə verin.

4. Yüksək Əlçatanlıq və Replikasiya Topologiyalarını Monitorinq Edin

AlwaysOn Əlçatanlıq Qruplarında, əsas replika jurnal qeydləri bütün sinxron və asinxron ikinci dərəcəli replikalarda bərkidilməyənə qədər jurnalını kəsə bilməz.

Əgər ikinci dərəcəli replika oflayn olarsa və ya şəbəkə bant genişliyi əsas replikanın tranzaksiya yaratma sürətinə çata bilmirsə, əsas replikanın göndərmə növbəsi böyüyəcək və jurnal dolacaq (AVAILABILITY_REPLICA gözləmə növü).

SQLServer:Replica > Log Send Queue performans sayğacı üçün güclü monitorinq tətbiq edin. Əgər ikinci dərəcəli replika daimi olaraq itirilərsə, onu Əlçatanlıq Qrupundan çıxarmalı və ya əsas jurnalın kəsilməsinə icazə vermək üçün məlumat hərəkətini dayandırmalısınız.

Nəticə

Dolu tranzaksiya jurnalı ilə qarşılaşmaq verilənlər bazası administratorları üçün bir sınaqdır, lakin bu, uzunmüddətli dayanma müddəti ilə nəticələnməməlidir. Write-Ahead Logging və VLF-lərin mexanikasını başa düşərək, sys.databases-dən istifadə edərək kök səbəbi tez bir zamanda diaqnoz edə və düzgün sürətli bərpa strategiyasını tətbiq edə bilərsiniz.

Uzunmüddətli sabitlik reaktiv düzəlişlərdən uzaqlaşmaqdan asılıdır. Jurnal fayllarınızı əvvəlcədən ölçmək, texniki xidmət rutinlərini optimallaşdırmaq və ciddi, avtomatlaşdırılmış jurnal ehtiyat nüsxə cədvəllərini tətbiq etmək üçün CloudSave kimi müəssisə səviyyəli ehtiyat nüsxə platformalarından istifadə etmək tranzaksiya jurnallarınızın sağlam, kəsilmiş və yüksək ötürücülü istehsal iş yüklərini dəstəkləməyə hazır qalmasını təmin edəcək.