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.

Microsoft SQL Server-ийг удирдаж буй Мэдээллийн сангийн администраторууд (DBA) болон DevOps инженерүүдийн хувьд “Алдаа 9002: ‘X’ мэдээллийн сангийн гүйлгээний бүртгэл (transaction log) дүүрсэн байна” гэсэн анхааруулгаас илүү их түгшүүр төрүүлдэг зүйл ховор. Гүйлгээний бүртгэл дүүрч, өсөх боломжгүй болох үед мэдээллийн сан нь зөвхөн унших горимд шилждэг. Бүх INSERT, UPDATE, болон DELETE үйлдлүүд зогсож, аппликейшны гүйлгээнүүд бүтэлгүйтэн, үйлдвэрлэлийн үйл ажиллагаа бүхэлдээ гацдаг.

SQL Server-ийн гүйлгээний бүртгэлийн архитектурыг ойлгох, үндсэн шалтгааныг нарийн оношлох, сэргээх хурдан арга хэмжээ авах нь өндөр хүртээмжтэй байдлыг хадгалахад чухал ур чадвар юм. Энэхүү иж бүрэн гарын авлагад гүйлгээний бүртгэлийн механизм, яаралтай үед дүүрсэн бүртгэлийг хэрхэн шийдвэрлэх, мөн дахин давтагдахаас сэргийлэх архитектурын шилдэг туршлагуудыг авч үзэх болно.

SQL Server-ийн гүйлгээний бүртгэлийн архитектурыг ойлгох

Дүүрсэн гүйлгээний бүртгэлийг үр дүнтэй шийдвэрлэхийн тулд та эхлээд SQL Server өгөгдлийг хэрхэн бичиж, удирддагийг ойлгох хэрэгтэй.

Урьдчилсан бичилтийн бүртгэл (Write-Ahead Logging – WAL)

SQL Server нь Урьдчилсан бичилтийн бүртгэл (WAL) протоколыг ашигладаг. Өгөгдөлд өөрчлөлт орох бүрд тухайн өөрчлөлтийг эхлээд санах ойд байгаа гүйлгээний бүртгэлд бичиж, дараа нь мэдээллийн сангийн файлууд (MDF/NDF) дээрх бодит өгөгдлийн хуудсуудыг шинэчлэхээс өмнө дискэн дээрх физик бүртгэлийн файл руу илгээдэг. Энэ нь ACID (Atomicity, Consistency, Isolation, Durability) стандартыг хангаж, системийн доголдлын үед SQL Server гүйлгээнүүдийг дахин тоглуулах (roll forward) эсвэл цуцлах (roll back) боломжийг олгодог.

Виртуал бүртгэлийн файлууд (VLFs) ба дугуй бүртгэл

Дотооддоо физик гүйлгээний бүртгэлийн файл (LDF) нь Виртуал бүртгэлийн файлууд (VLFs) гэж нэрлэгддэг жижиг логик сегментүүдэд хуваагддаг. Гүйлгээний бүртгэл нь дугуй хэлбэрээр ажилладаг. Бүртгэлийн бичилтүүд хийгдэх үед нэг VLF-ийг дүүргээд дараагийнх руу шилждэг.

Бүртгэл физик файлын төгсгөлд хүрэхэд эхлэл рүүгээ эргэн орохыг оролддог. Гэвч энэ нь зөвхөн идэвхгүй (inactive) гэж тэмдэглэгдсэн VLF-ийг л дарж бичих боломжтой. Хэрэв бүх VLF идэвхтэй байвал (өөрөөр хэлбэл SQL Server-т шаардлагатай бичилтүүдийг агуулж байвал) бүртгэл эргэн орох боломжгүй болно. Хэрэв автоматаар өсөх (auto-growth) тохиргоо идэвхтэй бөгөөд дискний зай байгаа бол физик файл томордог. Хэрэв диск дүүрсэн эсвэл автоматаар өсөх боломжгүй бол та 9002-р алдаатай тулгарна.

Бүртгэлийг таслах (Truncation) vs. Бүртгэлийг багасгах (Shrinking)

Бүртгэлийг таслах нь физик файлын хэмжээг багасгадаг гэсэн буруу ойлголт түгээмэл байдаг.
* Бүртгэлийг таслах (Log Truncation): Идэвхтэй VLF-үүдийг идэвхгүй болгож, зайг дахин ашиглах боломжтой болгох үйл явц. Энэ нь дискэн дээрх LDF файлын хэмжээг багасгадаггүй.
* Бүртгэлийг багасгах (Log Shrinking): LDF файлын хэмжээг физик байдлаар багасгаж, зайг үйлдлийн системд буцааж өгөх үйл явц.

Full Recovery загварт бүртгэлийг таслах үйл явц зөвхөн гүйлгээний бүртгэлийн нөөц хуулбар (transaction log backup) амжилттай хийгдсэний дараа л явагддаг (өөр ямар нэгэн процесс бүртгэлийг идэвхтэй байлгаагүй тохиолдолд).

“Гүйлгээний бүртгэл дүүрсэн” алдааг (Алдаа 9002) оношлох

Бүртгэл дүүрсэн үед таны хийх ёстой хамгийн эхний алхам бол дискний зайг нэмэх эсвэл файлыг багасгах биш юм. Та бүртгэл яагаад таслагдахгүй байгааг тодорхойлох ёстой. SQL Server нь sys.databases каталогийн харагдацыг ашиглан бүртгэлийг ашиглахад юу саад болж байгааг хэлж өгдөг.

Гацалтын шалтгааныг тодорхойлохын тулд дараах T-SQL командыг ажиллуулна уу:

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

Та мөн гүйлгээний бүртгэлийн одоогийн зайны ашиглалтыг дараах байдлаар шалгаж болно:

DBCC SQLPERF(LOGSPACE);

Нийтлэг log_reuse_wait_desc төлөвүүд

  1. LOG_BACKUP: Мэдээллийн сан Full эсвэл Bulk-Logged сэргээх загварт байгаа бөгөөд гүйлгээний бүртгэлийн нөөц хуулбарыг саяхан аваагүй байна. Энэ бол хамгийн түгээмэл шалтгаан юм.
  2. ACTIVE_TRANSACTION: Удаан хугацаанд үргэлжилж буй гүйлгээ (жишээ нь: индекс дахин бүтээх эсвэл баталгаажаагүй орхигдсон гүйлгээ) бүртгэлийг идэвхтэй байлгаж байна.
  3. REPLICATION / CDC: Transactional Replication эсвэл Change Data Capture (CDC) идэвхтэй байгаа бөгөөд Log Reader Agent гүйлгээнүүдийг хараахан боловсруулж амжаагүй байна.
  4. AVAILABILITY_REPLICA: AlwaysOn Availability Group-т хоёрдогч хуулбар (secondary replica) салсан эсвэл хэт удаан синхрончлогдож байгаа нь үндсэн хуулбарыг бүртгэлийн бичилтүүдийг хадгалахад хүргэж байна.

Сэргээх хурдан стратеги: Үйлдвэрлэлийн орчинд асуудлыг шийдвэрлэх

Буцаагдсан log_reuse_wait_desc-ээс хамааран таны яаралтай хариу арга хэмжээ өөр өөр байна. Хамгийн түгээмэл тохиолдлуудад зориулсан хурдан сэргээх стратегиуд энд байна.

Тохиолдол 1: Бүртгэлийн нөөц хуулбар дутуу эсвэл бүтэлгүйтсэн (LOG_BACKUP)

Хэрэв хүлээлтийн төрөл LOG_BACKUP бол шийдэл нь маш энгийн: та гүйлгээний бүртгэлийг нөөцлөх ёстой.

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

Нөөц хуулбар дууссаны дараа идэвхгүй VLF-үүд таслагдаж, SQL Server хэвийн ажиллагаандаа орно. Хэрэв таны нөөц хуулбарын диск дүүрсэн бол түр зуурын сүлжээний хавтас эсвэл null төхөөрөмж рүү нөөцлөх шаардлагатай байж магадгүй (мэдээллийн санг хялбархан сэргээх боломжгүй бол үүнийг зөвлөдөггүй, учир нь энэ нь бүртгэлийн гинжийг тасалдаг):

-- АНХААРУУЛГА: Энэ нь бүртгэлийн гинжийг тасалж, цаг хугацааны цэгийн сэргээлтийг эрсдэлд оруулна.
-- Зөвхөн зайлшгүй шаардлагатай үед ашиглаж, даруй FULL нөөц хуулбар хийгээрэй.
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';

Тохиолдол 2: Удаан үргэлжилж буй идэвхтэй гүйлгээнүүд (ACTIVE_TRANSACTION)

Хэрэв ганц гүйлгээ хэдэн цагийн турш үргэлжилж байгаа бол энэ нь бүртгэлийг таслахад саад болно. Эхлээд асуудал үүсгэж буй гүйлгээг тодорхойлно уу:

DBCC OPENTRAN('YourDatabaseName');

Энэ команд нь хамгийн эртний идэвхтэй гүйлгээ болон түүний Серверийн Процессын ID (SPID)-ийг буцаана. Та динамик удирдлагын харагдацуудыг (DMVs) ашиглан SPID юу хийж байгаа талаар дэлгэрэнгүй мэдээлэл авах боломжтой:

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

Хэрэв гүйлгээ нь буруу асуулга эсвэл гацсан процесс бол бүртгэлийг чөлөөлөхийн тулд түүнийг зогсоох шаардлагатай байж болно.

KILL <SPID>;

Тэмдэглэл: Том гүйлгээг зогсоох нь цуцлах (rollback) үйл явцыг эхлүүлэх бөгөөд энэ нь нэлээд хугацаа шаардаж, түр зуур нэмэлт бүртгэлийн үйл ажиллагаа үүсгэж болзошгүй. Цуцлах үйл явцын үед SQL Server үйлчилгээг дахин бүү асаагаарай, эс бөгөөс мэдээллийн сан дахин асах үед сэргээх горимд орно.

Тохиолдол 3: Яаралтай зай хуваарилах (Диск 100% дүүрсэн)

Хэрэв LDF файл бүх дискийг эзэлсэн бол та нөөц хуулбар ч хийх боломжгүй, учир нь SQL Server-т нөөц хуулбарын үйл явдлыг бүртгэхийн тулд бага зэрэг бүртгэлийн зай шаардлагатай байдаг. Энэ тохиолдолд та өөр диск дээр зай байгаа бол хоёрдогч бүртгэлийн файл нэмэх хэрэгтэй.

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

Энэ нь SQL Server-т нэн даруй ажиллах зайг олгоно. Мэдээллийн сан онлайн болсны дараа гүйлгээний бүртгэлийн нөөц хуулбар хийж, хоёрдогч бүртгэлийн файлыг хоосолж, устгана уу:

-- 1. Бүртгэлийг таслахын тулд нөөц хуулбар хийх
BACKUP LOG [YourDatabaseName] TO DISK = '...';

-- 2. Түр зуурын бүртгэлийн файлыг хоослох
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);

-- 3. Түр зуурын бүртгэлийн файлыг устгах
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];

Гүйлгээний бүртгэлийг урьдчилан сэргийлэх болон удирдах шилдэг туршлагууд

Асуудал гарсны дараа шийдвэрлэх нь стресстэй бөгөөд үйлчилгээний түвшний гэрээнд (SLA) нөлөөлдөг. Идэвхтэй архитектур болон үйл ажиллагааны шилдэг туршлагуудыг хэрэгжүүлэх нь байгууллагын мэдээллийн сангийн тогтвортой байдалд чухал юм.

1. Бат бөх, автоматжуулсан нөөц хуулбарын стратегийг хэрэгжүүлэх

Хэрэв мэдээллийн сан Full сэргээх загварт байгаа бол гүйлгээний бүртгэлийн нөөц хуулбарыг байнга хийх нь заавал биелүүлэх ёстой зүйл юм. Таны Сэргээх цэгийн зорилт (RPO) болон гүйлгээний хэмжээнээс хамааран бүртгэлийн нөөц хуулбарыг 5-15 минут тутамд хийх хэрэгтэй.

CloudSave гэх мэт байгууллагын нөөц хуулбарын шийдлүүд нь энэ үйл явцыг ихээхэн хялбаршуулдаг. VDI (Virtual Device Interface)-ээр дамжуулан SQL Server-тэй шууд холбогдсоноор CloudSave нь DBA-уудад бодлогод суурилсан, өндөр давтамжтай гүйлгээний бүртгэлийн нөөц хуулбарыг тохируулах боломжийг олгодог. Энэ нь бүртгэлүүдийг тасралтгүй тасалж, найдвартай шифрлэж, гадны эсвэл өөрчлөгдөшгүй үүлэн санд хадгалахыг баталгаажуулж, нарийн төвөгтэй SQL Agent ажлууд шаардахгүйгээр LOG_BACKUP хүлээлтийн төлөвөөс сэргийлдэг.

2. Гүйлгээний бүртгэлийн хэмжээг зөв тохируулах болон VLF-ийг удирдах

Гүйлгээний бүртгэлийн хэмжээг удирдахын тулд автоматаар өсөх (auto-growth) тохиргоонд найдах нь аюултай буруу арга юм. Автоматаар өсөх үйл ажиллагаа нь зардал ихтэй бөгөөд диск тэгээр эхлүүлэх (zero-initialized) үед гүйлгээний боловсруулалтыг түр зогсоодог (хэрэв Instant File Initialization идэвхтэй биш бол, энэ нь бүртгэлийн файлд хамаарахгүй).

Түүнчлэн, байнга жижиг хэмжээгээр автоматаар өсөх (жишээ нь: 10% эсвэл 50MB-аар) нь VLF фрагментацид хүргэдэг. Олон мянган жижиг VLF-тэй гүйлгээний бүртгэл нь мэдээллийн сангийн асах хугацаа, нөөц хуулбарын гүйцэтгэл болон репликацийн хоцрогдлыг эрс муутгадаг.

  • Бүртгэлийн хэмжээг урьдчилан тогтоох: Хамгийн том засвар үйлчилгээний үйлдлүүдээ (индекс дахин бүтээх гэх мэт) шинжилж, LDF файлын хэмжээг өсөх шаардлагагүйгээр багтахаар урьдчилан тогтоох.
  • Тогтмол автоматаар өсөх хэмжээг тохируулах: Автоматаар өсөх хэмжээг хувиар биш, тогтмол хэмжээгээр (жишээ нь: 1GB эсвэл 5GB) тохируулж, VLF-үүд эрүүл хэмжээтэй үүсэхийг баталгаажуулах.

Та VLF-ийн тоог дараах асуулгаар шалгаж болно (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'));

Хэрэв таны VLF-ийн тоо 500-аас дээш бол ачаалал багатай үеийг хүлээж, бүртгэлийг хамгийн бага хэмжээнд хүртэл багасгаад, дараа нь шаардлагатай хэмжээнд хүртэл том хэсгүүдээр гараар өсгөх талаар бодож үзээрэй.

3. Индексийн засвар үйлчилгээний үйлдлүүдийг оновчтой болгох

Индекс дахин бүтээх нь Bulk-Logged сэргээх загварт ч гэсэн бүрэн бүртгэгддэг үйлдэл юм (индексийн төрлөөс хамаарна). 500GB индекс дахин бүтээхэд дор хаяж 500GB гүйлгээний бүртгэлийн бичилт үүснэ.

Засвар үйлчилгээний үеэр бүртгэл хэт дүүрэхээс сэргийлэхийн тулд:
* Индекс дахин бүтээхдээ SORT_IN_TEMPDB = ON ашиглана уу. Энэ нь эрэмбэлэх үе шатыг TempDB рүү шилжүүлж, хэрэглэгчийн мэдээллийн сангийн гүйлгээний бүртгэлийн ачааллыг бууруулдаг.
* Боломжтой бол индекс дахин бүтээх (rebuild)-ээс индекс дахин зохион байгуулах (reorganize) руу шилжинэ үү, учир нь дахин зохион байгуулалт нь бүртгэлийн хувьд илүү үр ашигтай бөгөөд бүх үйлдлийг цуцлахгүйгээр тасалж болдог.
* Том DELETE эсвэл UPDATE үйлдлүүдийг багцалж гүйцэтгэнэ үү. 10 сая мөрийг нэг гүйлгээгээр устгахын оронд 50,000-аар багцалж устгаж, багц хооронд бүртгэлийн нөөц хуулбар хийж бүртгэлийг таслах боломж олгоно.

4. Өндөр хүртээмж болон репликацийн топологийг хянах

AlwaysOn Availability Groups-т үндсэн хуулбар нь бүртгэлийн бичилтүүд бүх синхрон болон асинхрон хоёрдогч хуулбарууд дээр баталгаажтал өөрийн бүртгэлийг таслах боломжгүй.

Хэрэв хоёрдогч хуулбар офлайн болсон эсвэл сүлжээний зурвасын өргөн нь үндсэн хуулбарын гүйлгээний хурдыг гүйцэхгүй бол үндсэн хуулбарын илгээх дараалал томорч, бүртгэл дүүрнэ (AVAILABILITY_REPLICA хүлээлтийн төрөл).

SQLServer:Replica > Log Send Queue гүйцэтгэлийн тоолуурт бат бөх хяналт тавина уу. Хэрэв хоёрдогч хуулбар бүрмөсөн алдагдсан бол та үүнийг Availability Group-ээс хасах эсвэл үндсэн бүртгэлийг таслах боломж олгохын тулд өгөгдлийн шилжилтийг түр зогсоох ёстой.

Дүгнэлт

Гүйлгээний бүртгэл дүүрэх нь мэдээллийн сангийн администраторуудын хувьд тулгардаг нийтлэг асуудал боловч энэ нь удаан хугацааны зогсолтод хүргэх албагүй. Урьдчилсан бичилтийн бүртгэл (WAL) болон VLF-ийн механизмыг ойлгосноор та sys.databases-ийг ашиглан үндсэн шалтгааныг хурдан оношилж, сэргээх зөв стратегийг хэрэгжүүлэх боломжтой.

Урт хугацааны тогтвортой байдал нь асуудал гарсны дараа шийдвэрлэхээс татгалзахаас хамаарна. Бүртгэлийн файлуудаа урьдчилан хэмжээг нь тогтоох, засвар үйлчилгээний горимуудыг оновчтой болгох, CloudSave гэх мэт байгууллагын түвшний нөөц хуулбарын платформуудыг ашиглан бүртгэлийн нөөц хуулбарын хатуу, автоматжуулсан хуваарийг мөрдөх нь таны гүйлгээний бүртгэлүүдийг эрүүл, таслагдсан, өндөр ачаалалтай үйлдвэрлэлийн ажлын ачааллыг дэмжихэд бэлэн байлгах болно.

Ангилал