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.

Ma’lumotlar bazasi ma’murlari (DBA) va Microsoft SQL Server’ni boshqaradigan DevOps muhandislari uchun 9002-xatolik: ‘X’ ma’lumotlar bazasi uchun tranzaksiya jurnali to’ldi kabi ogohlantirishlar juda katta xavotir uyg’otadi. Tranzaksiya jurnali to’lib qolganda va uni kengaytirish imkoni bo’lmaganda, ma’lumotlar bazasi amalda faqat o’qish rejimiga o’tadi. Barcha INSERT, UPDATE va DELETE operatsiyalari to’xtaydi, ilova tranzaksiyalari muvaffaqiyatsizlikka uchraydi va ishlab chiqarish jarayoni butunlay to’xtab qoladi.

SQL Server tranzaksiya jurnalining arxitekturasini tushunish, asosiy sababni aniq tashxislash va tezkor tiklash tartib-qoidalarini bajarish yuqori mavjudlikni (high availability) saqlash uchun muhim ko’nikmalardir. Ushbu qo’llanma tranzaksiya jurnalining mexanikasini, favqulodda vaziyatda to’lgan jurnalni qanday hal qilishni va uning qayta takrorlanishining oldini olish uchun arxitektura bo’yicha eng yaxshi amaliyotlarni o’rganadi.

SQL Server tranzaksiya jurnali arxitekturasini tushunish

To’lgan tranzaksiya jurnalini samarali bartaraf etish uchun avvalo SQL Server ma’lumotlarni qanday yozishi va boshqarishini tushunishingiz kerak.

Oldindan yozish jurnali (Write-Ahead Logging – WAL)

SQL Server «Oldindan yozish jurnali» (WAL) protokolidan foydalanadi. Ma’lumotlar o’zgartirilganda, o’zgarish avval xotiradagi tranzaksiya jurnaliga yoziladi, so’ngra ma’lumotlar bazasi fayllaridagi (MDF/NDF) haqiqiy ma’lumotlar sahifalari yangilanishidan oldin diskdagi jismoniy jurnal fayliga o’tkaziladi. Bu ACID (Atomicity, Consistency, Isolation, Durability) muvofiqligini kafolatlaydi va tizim ishdan chiqqan taqdirda SQL Server tranzaksiyalarni qayta ijro etishi (roll forward) yoki bekor qilishi (roll back) mumkinligini ta’minlaydi.

Virtual jurnal fayllari (VLFs) va aylanma jurnal yuritish

Ichki jihatdan, jismoniy tranzaksiya jurnali fayli (LDF) Virtual jurnal fayllari (VLFs) deb ataladigan kichikroq, mantiqiy segmentlarga bo’linadi. Tranzaksiya jurnali aylanma tarzda ishlaydi. Jurnal yozuvlari yozilganda, ular bitta VLFni to’ldiradi va keyingisiga o’tadi.

Jurnal jismoniy faylning oxiriga yetganda, u boshiga qaytishga harakat qiladi. Biroq, u VLFni faqat u noaktiv deb belgilangan bo’lsa, ustiga yozishi mumkin. Agar barcha VLFlar faol bo’lsa (ya’ni ular SQL Server uchun hali ham kerak bo’lgan jurnal yozuvlarini o’z ichiga olsa), jurnal qayta aylana olmaydi. Agar avtomatik o’sish (auto-growth) yoqilgan bo’lsa va diskda bo’sh joy mavjud bo’lsa, jismoniy fayl o’sadi. Agar disk to’lgan bo’lsa yoki avtomatik o’sish cheklangan bo’lsa, siz 9002-xatolikka duch kelasiz.

Jurnalni qisqartirish (Truncation) va jurnal hajmini kamaytirish (Shrinking)

Keng tarqalgan noto’g’ri tushuncha shundaki, jurnalni qisqartirish jismoniy fayl hajmini kamaytiradi.
* Jurnalni qisqartirish (Log Truncation): Faol VLF’larni noaktiv deb belgilash jarayoni, bu joyni qayta foydalanish uchun mavjud qiladi. Bu diskdagi LDF faylining hajmini kamaytirmaydi.
* Jurnal hajmini kamaytirish (Log Shrinking): LDF fayli hajmini jismonan kamaytirish va operatsion tizimga joyni qaytarish jarayoni.

To’liq tiklash modelida (Full Recovery model), jurnalni qisqartirish faqat tranzaksiya jurnali zaxira nusxasi muvaffaqiyatli yakunlanganda sodir bo’ladi (agar boshqa jarayonlar jurnalni faol ushlab turmasa).

«Tranzaksiya jurnali to’ldi» xatoligini (9002-xatolik) tashxislash

Jurnal to’lganida, birinchi qadamingiz ko’r-ko’rona disk joyini qo’shish yoki fayllarni qisqartirish bo’lmasligi kerak. Siz jurnal nima uchun qisqartirilmayotganini aniqlashingiz kerak. SQL Server sys.databases katalog ko’rinishi orqali jurnalni qayta ishlatishga nima to’sqinlik qilayotganini aniq aytib beradigan o’rnatilgan mexanizmni taqdim etadi.

To’siqni aniqlash uchun quyidagi T-SQL buyrug’ini bajaring:

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

Shuningdek, tranzaksiya jurnallaringizning joriy joydan foydalanishini quyidagi buyruq bilan tekshirishingiz mumkin:

DBCC SQLPERF(LOGSPACE);

Umumiy log_reuse_wait_desc holatlari

  1. LOG_BACKUP: Ma’lumotlar bazasi «Full» yoki «Bulk-Logged» tiklash modelida va tranzaksiya jurnali zaxira nusxasi yaqinda olinmagan. Bu eng keng tarqalgan sababdir.
  2. ACTIVE_TRANSACTION: Uzoq davom etayotgan tranzaksiya (masalan, indeksni qayta qurish yoki tasdiqlanmagan tranzaksiya) jurnalni faol ushlab turibdi.
  3. REPLICATION / CDC: Tranzaksion replikatsiya yoki Change Data Capture (CDC) yoqilgan va Log Reader Agent hali tranzaksiyalarni qayta ishlamagan.
  4. AVAILABILITY_REPLICA: AlwaysOn Availability Group’da ikkilamchi replika uzilgan yoki juda sekin sinxronlanmoqda, bu esa birlamchi replikani jurnal yozuvlarini ikkilamchi replikada tasdiqlanmaguncha saqlab turishga majbur qiladi.

Tezkor tiklash strategiyalari: Ishlab chiqarish muhitida muammoni hal qilish

Qaytarilgan log_reuse_wait_desc holatiga qarab, favqulodda vaziyatdagi harakatlaringiz turlicha bo’ladi. Quyida eng keng tarqalgan stsenariylar uchun tezkor tiklash strategiyalari keltirilgan.

1-stsenariy: Jurnal zaxira nusxalarining yo’qligi yoki muvaffaqiyatsizligi (LOG_BACKUP)

Agar kutish turi LOG_BACKUP bo’lsa, yechim oddiy: tranzaksiya jurnalining zaxira nusxasini olishingiz kerak.

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

Zaxira nusxasi tugagach, noaktiv VLF’lar qisqartiriladi va SQL Server normal ishlashni davom ettiradi. Agar zaxira nusxasi uchun disk to’lgan bo’lsa, siz vaqtinchalik tarmoq ulushiga yoki null qurilmaga zaxira qilishingiz mumkin (agar ma’lumotlar bazasini osongina qayta tiklash imkoni bo’lmasa, buni tavsiya etmaymiz, chunki bu jurnal zanjirini uzadi):

-- OGOHLANTIRISH: Bu jurnal zanjirini uzadi va vaqt bo'yicha tiklashni xavf ostiga qo'yadi.
-- Faqat mutlaqo zarur bo'lganda foydalaning va darhol FULL zaxira nusxasini oling.
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';

2-stsenariy: Uzoq davom etayotgan faol tranzaksiyalar (ACTIVE_TRANSACTION)

Agar bitta tranzaksiya soatlab davom etayotgan bo’lsa, u butun vaqt davomida jurnalni qisqartirishga to’sqinlik qiladi. Avvalo, muammoli tranzaksiyani aniqlang:

DBCC OPENTRAN('YourDatabaseName');

Ushbu buyruq eng qadimgi faol tranzaksiyani va uning Server jarayon identifikatorini (SPID) qaytaradi. Dinamik boshqaruv ko’rinishlarini (DMVs) so’rov qilish orqali SPID nima qilayotgani haqida ko’proq ma’lumot olishingiz mumkin:

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

Agar tranzaksiya noto’g’ri so’rov yoki to’xtab qolgan jarayon bo’lsa, jurnalni bo’shatish uchun uni to’xtatishingiz kerak bo’lishi mumkin.

KILL <SPID>;

Eslatma: Katta tranzaksiyani o’ldirish (KILL) qaytarish (rollback) jarayonini ishga tushiradi, bu sezilarli vaqt olishi mumkin va vaqtincha qo’shimcha jurnal faoliyatini keltirib chiqaradi. Qaytarish jarayonida SQL Server xizmatini qayta ishga tushirmang, aks holda ma’lumotlar bazasi qayta ishga tushirilganda tiklash rejimiga o’tadi.

3-stsenariy: Favqulodda joy ajratish (Disk 100% to’lgan)

Agar LDF fayli butun diskni egallagan bo’lsa, siz zaxira nusxasini ham ololmaysiz, chunki SQL Server zaxira hodisasini yozib olish uchun ozgina jurnal joyiga muhtoj. Bunday holda, siz boshqa diskda bo’sh joyi bo’lgan ikkilamchi jurnal faylini qo’shishingiz kerak.

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

Bu darhol SQL Serverga nafas olish imkonini beradi. Ma’lumotlar bazasi onlayn bo’lgach, tranzaksiya jurnali zaxira nusxasini oling, ikkilamchi jurnal faylini bo’shating va uni olib tashlang:

-- 1. Jurnalni qisqartirish uchun log zaxira nusxasini oling
BACKUP LOG [YourDatabaseName] TO DISK = '...';

-- 2. Vaqtinchalik jurnal faylini bo'shating
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);

-- 3. Vaqtinchalik jurnal faylini olib tashlang
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];

Tranzaksiya jurnalini oldini olish va boshqarish bo’yicha eng yaxshi amaliyotlar

Reaktiv muammolarni bartaraf etish stressli va SLA’larga ta’sir qiladi. Korporativ ma’lumotlar bazasi barqarorligi uchun proaktiv arxitektura va operatsion eng yaxshi amaliyotlarni joriy etish juda muhimdir.

1. Mustahkam, avtomatlashtirilgan zaxira strategiyasini joriy eting

Agar ma’lumotlar bazasi «Full» tiklash modelida bo’lsa, tez-tez tranzaksiya jurnali zaxira nusxalarini olish majburiydir. Tiklash nuqtasi maqsadingiz (RPO) va tranzaksiya hajmiga qarab, jurnal zaxira nusxalari har 5-15 daqiqada olinishi kerak.

CloudSave kabi korporativ zaxira yechimlari bu jarayonni sezilarli darajada soddalashtiradi. VDI (Virtual Device Interface) orqali SQL Server bilan to’g’ridan-to’g’ri integratsiyalashgan holda, CloudSave DBA’larga siyosatga asoslangan, yuqori chastotali tranzaksiya jurnali zaxira nusxalarini sozlash imkonini beradi. Bu jurnallarning doimiy ravishda qisqartirilishini, xavfsiz shifrlanishini va tashqi yoki o’zgarmas bulutli xotirada saqlanishini ta’minlaydi, bu esa murakkab SQL Agent ishlarini talab qilmasdan LOG_BACKUP kutish holatining oldini oladi.

2. Tranzaksiya jurnalini to’g’ri o’lchamda saqlang va VLF’larni boshqaring

Tranzaksiya jurnali hajmini boshqarish uchun avtomatik o’sishga (auto-growth) tayanish xavfli yondashuvdir. Avtomatik o’sish operatsiyalari qimmat va disk nol bilan ishga tushirilguncha tranzaksiya ishlovini to’xtatib turadi (agar Instant File Initialization yoqilmagan bo’lsa, bu jurnal fayllariga taalluqli emas).

Bundan tashqari, tez-tez sodir bo’ladigan kichik avtomatik o’sishlar (masalan, har safar 10% yoki 50MB ga o’sish) VLF fragmentatsiyasiga olib keladi. Minglab kichik VLF’larga ega tranzaksiya jurnali ma’lumotlar bazasini ishga tushirish vaqtini, zaxira nusxasi unumdorligini va replikatsiya kechikishini keskin yomonlashtiradi.

  • Jurnalni oldindan o’lchamlang: Eng katta texnik xizmat ko’rsatish operatsiyalaringizni (masalan, indeksni qayta qurish) tahlil qiling va LDF faylini o’sishga hojat qoldirmasdan sig’adigan qilib oldindan o’lchamlang.
  • Belgilangan avtomatik o’sishni o’rnating: Avtomatik o’sishni foizdan belgilangan hajmga (masalan, 1GB yoki 5GB) o’zgartiring, bu VLF’larning sog’lom hajmda yaratilishini ta’minlaydi.

Siz VLF sonini quyidagi so’rov orqali tekshirishingiz mumkin (SQL Server 2017+ uchun):

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

Agar VLF soningiz 500 dan oshsa, tinch davrni kutib, jurnalni minimal hajmga qisqartiring va uni qo’lda katta qismlar bilan kerakli hajmga qaytaring.

3. Indeksga texnik xizmat ko’rsatish operatsiyalarini optimallashtiring

Indeksni qayta qurish (rebuild) operatsiyalari, hatto «Bulk-Logged» tiklash modelida ham (indeks turiga qarab) to’liq jurnalga yoziladi. 500GB indeksni qayta qurish kamida 500GB tranzaksiya jurnali yozuvlarini hosil qiladi.

Texnik xizmat ko’rsatish vaqtida jurnalning haddan tashqari ko’payishini yumshatish uchun:
* Indekslarni qayta qurishda SORT_IN_TEMPDB = ON dan foydalaning. Bu saralash bosqichini TempDB’ga o’tkazadi va foydalanuvchi ma’lumotlar bazasining tranzaksiya jurnaliga yukni kamaytiradi.
* Iloji bo’lsa, indeksni qayta qurishdan (rebuild) indeksni qayta tashkil etishga (reorganize) o’ting, chunki qayta tashkil etish jurnal uchun samaraliroq va butun operatsiyani qaytarmasdan to’xtatilishi mumkin.
* Katta DELETE yoki UPDATE operatsiyalarini partiyalarga bo’ling. 10 million qatorni bitta tranzaksiyada o’chirish o’rniga, ularni 50 000 tadan qilib o’chiring, tasdiqlang va partiyalar orasida jurnal zaxira nusxalariga jurnalni qisqartirishga ruxsat bering.

4. Yuqori mavjudlik va replikatsiya topologiyalarini kuzating

AlwaysOn Availability Group’da birlamchi replika jurnal yozuvlari barcha sinxron va asinxron ikkilamchi replikalarda tasdiqlanmaguncha o’z jurnalini qisqartira olmaydi.

Agar ikkilamchi replika oflayn rejimga o’tsa yoki tarmoq o’tkazuvchanligi birlamchi replikaning tranzaksiya hosil qilish tezligiga mos kela olmasa, birlamchi replikaning yuborish navbati o’sadi va jurnal to’lib qoladi (AVAILABILITY_REPLICA kutish turi).

SQLServer:Replica > Log Send Queue ishlash ko’rsatkichi uchun mustahkam monitoringni joriy eting. Agar ikkilamchi replika butunlay yo’qolsa, birlamchi jurnal qisqartirilishi uchun uni Availability Group’dan olib tashlashingiz yoki ma’lumotlar harakatini to’xtatib turishingiz kerak.

Xulosa

To’lgan tranzaksiya jurnaliga duch kelish ma’lumotlar bazasi ma’murlari uchun odatiy hol, ammo bu uzoq muddatli ishlamay qolishga olib kelishi shart emas. «Oldindan yozish jurnali» va VLF mexanikasini tushunib, siz sys.databases yordamida asosiy sababni tezda tashxislashingiz va to’g’ri tezkor tiklash strategiyasini qo’llashingiz mumkin.

Uzoq muddatli barqarorlik reaktiv tuzatishlardan voz kechishga bog’liq. Jurnal fayllaringizni oldindan o’lchamlash, texnik xizmat ko’rsatish tartiblarini optimallashtirish va qat’iy, avtomatlashtirilgan jurnal zaxira nusxasi jadvallarini bajarish uchun CloudSave kabi korporativ darajadagi zaxira platformalaridan foydalanish tranzaksiya jurnallaringizning sog’lom, qisqartirilgan va yuqori unumdorlikdagi ishlab chiqarish yuklamalarini qo’llab-quvvatlashga tayyor bo’lishini ta’minlaydi.

Bo’limlar