Categories
Database Backup
  • Post author By shervinrv
  • มิถุนายน 16, 2026

** 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.

สำหรับผู้ดูแลระบบฐานข้อมูล (DBA) และวิศวกร DevOps ที่จัดการ Microsoft SQL Server มีการแจ้งเตือนเพียงไม่กี่อย่างที่สร้างความวิตกกังวลได้ทันทีเท่ากับ Error 9002: The transaction log for database ‘X’ is full เมื่อ Transaction Log เต็มและไม่สามารถขยายขนาดได้ ฐานข้อมูลจะกลายเป็นแบบอ่านอย่างเดียว (Read-only) โดยสมบูรณ์ การดำเนินการ INSERT, UPDATE และ DELETE ทั้งหมดจะหยุดชะงัก ธุรกรรมของแอปพลิเคชันจะล้มเหลว และการทำงานในระบบผลิตจริงจะหยุดนิ่ง

การเข้าใจสถาปัตยกรรมพื้นฐานของ SQL Server Transaction Log การวินิจฉัยสาเหตุที่แท้จริงอย่างแม่นยำ และการดำเนินการตามขั้นตอนการกู้คืนอย่างรวดเร็ว เป็นทักษะที่สำคัญสำหรับการรักษาความพร้อมใช้งานสูง (High Availability) คู่มือฉบับสมบูรณ์นี้จะสำรวจกลไกของ Transaction Log วิธีแก้ไขปัญหา Log เต็มในกรณีฉุกเฉิน และแนวทางปฏิบัติที่ดีที่สุดทางสถาปัตยกรรมเพื่อป้องกันไม่ให้เหตุการณ์นี้เกิดขึ้นอีก

ทำความเข้าใจสถาปัตยกรรม SQL Server Transaction Log

เพื่อให้สามารถแก้ไขปัญหา Transaction Log เต็มได้อย่างมีประสิทธิภาพ คุณต้องเข้าใจก่อนว่า SQL Server เขียนและจัดการข้อมูลอย่างไร

Write-Ahead Logging (WAL)

SQL Server ใช้โปรโตคอล Write-Ahead Logging (WAL) เมื่อใดก็ตามที่มีการแก้ไขข้อมูล การเปลี่ยนแปลงนั้นจะถูกเขียนลงใน Transaction Log ในหน่วยความจำก่อน จากนั้นจึงจะถูกบันทึกลงในไฟล์ Log จริงบนดิสก์ ก่อนที่หน้าข้อมูลจริง (Data Pages) จะถูกอัปเดตในไฟล์ฐานข้อมูล (MDF/NDF) สิ่งนี้รับประกันการปฏิบัติตามหลัก ACID (Atomicity, Consistency, Isolation, Durability) เพื่อให้มั่นใจว่าในกรณีที่ระบบขัดข้อง SQL Server จะสามารถเล่นซ้ำ (Roll forward) หรือยกเลิก (Roll back) ธุรกรรมได้

Virtual Log Files (VLFs) และ Circular Logging

ภายในไฟล์ Transaction Log จริง (LDF) จะถูกแบ่งออกเป็นส่วนย่อยๆ ที่เรียกว่า Virtual Log Files (VLFs) โดย Transaction Log จะทำงานแบบวงกลม เมื่อมีการเขียนบันทึก Log มันจะเติมเต็ม VLF หนึ่งแล้วย้ายไปยังถัดไป

เมื่อ Log ไปถึงจุดสิ้นสุดของไฟล์จริง มันจะพยายามวนกลับไปยังจุดเริ่มต้น อย่างไรก็ตาม มันสามารถเขียนทับ VLF ได้ก็ต่อเมื่อ VLF นั้นถูกทำเครื่องหมายว่า ไม่ใช้งาน (Inactive) เท่านั้น หาก VLF ทั้งหมดอยู่ในสถานะใช้งาน (หมายความว่ายังมีบันทึก Log ที่ SQL Server จำเป็นต้องใช้อยู่) Log จะไม่สามารถวนกลับได้ หากเปิดใช้งานการขยายขนาดอัตโนมัติ (Auto-growth) และมีพื้นที่ดิสก์เหลืออยู่ ไฟล์จริงจะขยายขนาดขึ้น แต่ถ้าดิสก์เต็มหรือการขยายขนาดอัตโนมัติถูกจำกัด คุณจะพบกับ Error 9002

การตัด Log (Log Truncation) เทียบกับการย่อขนาด Log (Log Shrinking)

ความเข้าใจผิดที่พบบ่อยคือการตัด Log จะช่วยลดขนาดไฟล์จริง
* Log Truncation: กระบวนการทำเครื่องหมาย VLF ที่ใช้งานอยู่ให้เป็นไม่ใช้งาน เพื่อให้พื้นที่ว่างสามารถนำกลับมาใช้ใหม่ได้ ซึ่ง ไม่ เป็นการลดขนาดไฟล์ LDF บนดิสก์
* Log Shrinking: กระบวนการลดขนาดไฟล์ LDF จริงและคืนพื้นที่ให้กับระบบปฏิบัติการ

ในรูปแบบการกู้คืนแบบ Full (Full Recovery model) การตัด Log จะเกิดขึ้น ก็ต่อเมื่อ การสำรองข้อมูล Transaction Log เสร็จสมบูรณ์เท่านั้น (โดยสมมติว่าไม่มีกระบวนการอื่นที่ถือครอง Log ไว้อยู่)

การวินิจฉัยข้อผิดพลาด “Transaction Log Full” (Error 9002)

เมื่อ Log เต็ม ขั้นตอนแรกของคุณไม่ใช่การเพิ่มพื้นที่ดิสก์หรือย่อขนาดไฟล์แบบสุ่มสี่สุ่มห้า คุณต้องระบุให้ได้ว่า ทำไม Log ถึงไม่สามารถตัดได้ SQL Server มีกลไกในตัวเพื่อบอกคุณว่าอะไรคือสาเหตุที่ขัดขวางการนำ Log กลับมาใช้ใหม่ผ่านมุมมองแคตตาล็อก 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';

คุณยังสามารถตรวจสอบการใช้พื้นที่ปัจจุบันของ Transaction Log ของคุณได้โดยใช้:

DBCC SQLPERF(LOGSPACE);

สถานะ log_reuse_wait_desc ที่พบบ่อย

  1. LOG_BACKUP: ฐานข้อมูลอยู่ในรูปแบบการกู้คืนแบบ Full หรือ Bulk-Logged และไม่ได้ทำการสำรองข้อมูล Transaction Log มาเป็นเวลานาน นี่เป็นสาเหตุที่พบบ่อยที่สุด
  2. ACTIVE_TRANSACTION: ธุรกรรมที่ทำงานยาวนาน (เช่น การสร้างดัชนีใหม่ขนาดใหญ่ หรือธุรกรรมที่ลืมคอมมิต) กำลังทำให้ Log ยังคงสถานะใช้งานอยู่
  3. REPLICATION / CDC: มีการเปิดใช้งาน Transactional Replication หรือ Change Data Capture (CDC) และ Log Reader Agent ยังไม่ได้ประมวลผลธุรกรรมเหล่านั้น
  4. AVAILABILITY_REPLICA: ใน AlwaysOn Availability Group มี Secondary Replica ที่ตัดการเชื่อมต่อหรือซิงโครไนซ์ช้าเกินไป ทำให้ Primary Replica ต้องเก็บรักษาบันทึก Log ไว้จนกว่าจะถูกบันทึกลงใน Secondary

กลยุทธ์การกู้คืนอย่างรวดเร็ว: การแก้ไขปัญหาในระบบผลิตจริง

ขึ้นอยู่กับค่า log_reuse_wait_desc ที่ได้รับ การตอบสนองต่อเหตุฉุกเฉินของคุณจะแตกต่างกันไป นี่คือกลยุทธ์การกู้คืนอย่างรวดเร็วสำหรับสถานการณ์ที่พบบ่อยที่สุด

สถานการณ์ที่ 1: ขาดการสำรองข้อมูล Log หรือการสำรองข้อมูลล้มเหลว (LOG_BACKUP)

หากประเภทการรอคือ LOG_BACKUP วิธีแก้ไขนั้นตรงไปตรงมา: คุณต้องสำรองข้อมูล Transaction Log

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

เมื่อการสำรองข้อมูลเสร็จสิ้น VLF ที่ไม่ใช้งานจะถูกตัดออก และ SQL Server จะกลับมาทำงานตามปกติ หากไดรฟ์สำรองข้อมูลของคุณเต็ม คุณอาจต้องสำรองข้อมูลไปยัง Network Share ชั่วคราวหรืออุปกรณ์ Null (ไม่แนะนำอย่างยิ่งเว้นแต่ฐานข้อมูลจะสร้างใหม่ได้ง่าย เพราะมันจะทำลายห่วงโซ่ของ Log):

-- คำเตือน: สิ่งนี้จะทำลายห่วงโซ่ของ Log และส่งผลต่อการกู้คืน ณ จุดเวลาที่กำหนด
-- ใช้เฉพาะเมื่อจำเป็นจริงๆ เท่านั้น และต้องตามด้วยการสำรองข้อมูลแบบ FULL ทันที
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';

สถานการณ์ที่ 2: ธุรกรรมที่ทำงานยาวนาน (ACTIVE_TRANSACTION)

หากธุรกรรมเดียวทำงานมานานหลายชั่วโมง มันจะป้องกันการตัด Log ตลอดระยะเวลานั้น ก่อนอื่นให้ระบุธุรกรรมที่เป็นปัญหา:

DBCC OPENTRAN('YourDatabaseName');

คำสั่งนี้จะส่งคืนธุรกรรมที่ใช้งานอยู่เก่าที่สุดและ Server Process ID (SPID) ของมัน คุณสามารถรวบรวมรายละเอียดเพิ่มเติมเกี่ยวกับสิ่งที่ SPID นั้นกำลังทำอยู่โดยการสอบถาม 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>;

หากธุรกรรมนั้นเป็นคิวรีที่ไม่พึงประสงค์หรือกระบวนการที่ค้างอยู่ คุณอาจต้องยุติการทำงานเพื่อคืนพื้นที่ Log

KILL <SPID>;

หมายเหตุ: การยุติธุรกรรมขนาดใหญ่จะกระตุ้นให้เกิดการ Rollback ซึ่งอาจใช้เวลานานและจะสร้างกิจกรรม Log เพิ่มเติมชั่วคราว ห้ามรีสตาร์ทบริการ SQL Server ในระหว่างการ Rollback มิฉะนั้นฐานข้อมูลจะเข้าสู่โหมดกู้คืนเมื่อรีสตาร์ท

สถานการณ์ที่ 3: การจัดสรรพื้นที่ฉุกเฉิน (ดิสก์เต็ม 100%)

หากไฟล์ LDF กินพื้นที่ไดรฟ์จนเต็ม คุณจะไม่สามารถแม้แต่จะรันการสำรองข้อมูลได้ เพราะ SQL Server ต้องการพื้นที่ Log เล็กน้อยเพื่อบันทึกเหตุการณ์การสำรองข้อมูลเอง ในสถานการณ์นี้ คุณต้องเพิ่มไฟล์ Log สำรองบนไดรฟ์อื่นที่มีพื้นที่ว่าง

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

สิ่งนี้จะช่วยให้ SQL Server มีพื้นที่หายใจทันที เมื่อฐานข้อมูลออนไลน์แล้ว ให้ทำการสำรองข้อมูล Transaction Log ล้างไฟล์ Log สำรอง และลบออก:

-- 1. สำรองข้อมูล Log เพื่อตัด Log
BACKUP LOG [YourDatabaseName] TO DISK = '...';

-- 2. ล้างไฟล์ Log ชั่วคราว
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);

-- 3. ลบไฟล์ Log ชั่วคราว
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];

แนวทางปฏิบัติที่ดีที่สุดสำหรับการป้องกันและจัดการ Transaction Log

การแก้ไขปัญหาแบบตั้งรับนั้นสร้างความเครียดและส่งผลต่อ SLA การนำแนวทางปฏิบัติที่ดีที่สุดทางสถาปัตยกรรมและการดำเนินงานเชิงรุกมาใช้เป็นสิ่งจำเป็นสำหรับความเสถียรของฐานข้อมูลระดับองค์กร

1. ใช้กลยุทธ์การสำรองข้อมูลอัตโนมัติที่แข็งแกร่ง

หากฐานข้อมูลอยู่ในรูปแบบการกู้คืนแบบ Full การสำรองข้อมูล Transaction Log บ่อยครั้งเป็นสิ่งที่จำเป็น ขึ้นอยู่กับเป้าหมายจุดกู้คืน (RPO) และปริมาณธุรกรรมของคุณ การสำรองข้อมูล Log ควรเกิดขึ้นทุกๆ 5 ถึง 15 นาที

โซลูชันการสำรองข้อมูลระดับองค์กรอย่าง CloudSave ช่วยลดความซับซ้อนของกระบวนการนี้ได้อย่างมาก ด้วยการรวมเข้ากับ SQL Server โดยตรงผ่าน VDI (Virtual Device Interface) CloudSave ช่วยให้ DBA สามารถกำหนดค่าการสำรองข้อมูล Transaction Log ที่มีความถี่สูงตามนโยบาย สิ่งนี้ช่วยให้มั่นใจได้ว่า Log จะถูกตัดออกอย่างต่อเนื่อง เข้ารหัสอย่างปลอดภัย และจัดเก็บไว้นอกสถานที่หรือในที่จัดเก็บข้อมูลบนคลาวด์ที่ไม่สามารถแก้ไขได้ ป้องกันสถานะการรอ LOG_BACKUP โดยไม่ต้องใช้ SQL Agent Jobs ที่ซับซ้อน

2. ปรับขนาด Transaction Log และจัดการ VLF ให้เหมาะสม

การพึ่งพาการขยายขนาดอัตโนมัติ (Auto-growth) เพื่อจัดการขนาด Transaction Log เป็นรูปแบบที่ไม่ดีและอันตราย การดำเนินการขยายขนาดอัตโนมัติมีราคาแพงและจะหยุดการประมวลผลธุรกรรมในขณะที่ดิสก์ถูกเริ่มต้นด้วยศูนย์ (เว้นแต่จะเปิดใช้งาน Instant File Initialization ซึ่ง ไม่ ใช้กับไฟล์ Log)

นอกจากนี้ การขยายขนาดอัตโนมัติที่บ่อยและเล็กน้อย (เช่น เพิ่มทีละ 10% หรือ 50MB) จะนำไปสู่ VLF fragmentation Transaction Log ที่มี VLF ขนาดเล็กจำนวนมากจะทำให้เวลาเริ่มต้นฐานข้อมูล ประสิทธิภาพการสำรองข้อมูล และความหน่วงของการทำ Replication ลดลงอย่างมาก

  • กำหนดขนาด Log ล่วงหน้า: วิเคราะห์การดำเนินการบำรุงรักษาที่ใหญ่ที่สุดของคุณ (เช่น การสร้างดัชนีใหม่) และกำหนดขนาดไฟล์ 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 ให้พิจารณารอช่วงเวลาที่เงียบสงบ ย่อขนาด Log ให้เหลือขนาดเล็กที่สุด และขยายกลับไปยังขนาดที่ต้องการด้วยตนเองในขนาดที่ใหญ่ขึ้น

3. เพิ่มประสิทธิภาพการดำเนินการบำรุงรักษาดัชนี

การสร้างดัชนีใหม่ (Index rebuilds) เป็นการดำเนินการที่มีการบันทึก Log เต็มรูปแบบ แม้ในรูปแบบการกู้คืนแบบ Bulk-Logged (ขึ้นอยู่กับประเภทดัชนี) การสร้างดัชนีขนาด 500GB ใหม่จะสร้างบันทึก Transaction Log อย่างน้อย 500GB

เพื่อลดปัญหา Log บวมระหว่างการบำรุงรักษา:
* ใช้ SORT_IN_TEMPDB = ON เมื่อสร้างดัชนีใหม่ สิ่งนี้จะย้ายขั้นตอนการเรียงลำดับไปยัง TempDB ลดภาระใน Transaction Log ของฐานข้อมูลผู้ใช้
* เปลี่ยนจากการ สร้างดัชนีใหม่ (rebuilds) เป็นการ จัดระเบียบดัชนี (reorganizes) หากเป็นไปได้ เนื่องจากการจัดระเบียบมีประสิทธิภาพในการใช้ Log มากกว่าและสามารถหยุดชะงักได้โดยไม่ต้องย้อนกลับการดำเนินการทั้งหมด
* แบ่งการดำเนินการ DELETE หรือ UPDATE ขนาดใหญ่เป็นชุดๆ แทนที่จะลบ 10 ล้านแถวในธุรกรรมเดียว ให้ลบทีละ 50,000 แถว โดยคอมมิตและปล่อยให้การสำรองข้อมูล Log ตัด Log ระหว่างชุดเหล่านั้น

4. ตรวจสอบความพร้อมใช้งานสูงและโทโพโลยีการทำ Replication

ใน AlwaysOn Availability Groups, Primary Replica จะไม่สามารถตัด Log ได้จนกว่าบันทึก Log จะถูกบันทึกลงใน Secondary Replica ทั้งแบบซิงโครนัสและอะซิงโครนัสทั้งหมด

หาก Secondary Replica ออฟไลน์ หรือหากแบนด์วิดท์เครือข่ายไม่สามารถตามอัตราการสร้างธุรกรรมของ Primary ได้ คิวการส่งของ Primary จะเพิ่มขึ้น และ Log จะเต็ม (ประเภทการรอ AVAILABILITY_REPLICA)

ใช้การตรวจสอบที่แข็งแกร่งสำหรับตัวนับประสิทธิภาพ SQLServer:Replica > Log Send Queue หาก Secondary Replica สูญหายอย่างถาวร คุณต้องลบออกจาก Availability Group หรือระงับการย้ายข้อมูลเพื่อให้ Log ของ Primary สามารถตัดได้

บทสรุป

การพบกับ Transaction Log เต็มเป็นเรื่องปกติสำหรับผู้ดูแลระบบฐานข้อมูล แต่ไม่จำเป็นต้องส่งผลให้เกิดการหยุดทำงานเป็นเวลานาน ด้วยการเข้าใจกลไกของ Write-Ahead Logging และ VLF คุณสามารถวินิจฉัยสาเหตุที่แท้จริงได้อย่างรวดเร็วโดยใช้ sys.databases และใช้กลยุทธ์การกู้คืนอย่างรวดเร็วที่ถูกต้อง

ความเสถียรในระยะยาวขึ้นอยู่กับการเปลี่ยนจากการแก้ไขปัญหาแบบตั้งรับ การกำหนดขนาดไฟล์ Log ล่วงหน้า การเพิ่มประสิทธิภาพกิจวัตรการบำรุงรักษา และการใช้แพลตฟอร์มการสำรองข้อมูลระดับองค์กรอย่าง CloudSave เพื่อบังคับใช้ตารางการสำรองข้อมูล Log ที่เข้มงวดและอัตโนมัติ จะช่วยให้มั่นใจได้ว่า Transaction Log ของคุณจะยังคงมีสุขภาพดี ถูกตัดออก และพร้อมที่จะรองรับปริมาณงานผลิตที่มีประสิทธิภาพสูง

หมวดหมู่