[MSSQL] Database log file คือ อะไร

สำหรับฐานข้อมูลของทุกๆค่าย ตัวเนื้อของ Database มีไฟล์ที่สำคัญอยู่ 2 ตัว  ได้แก่

• ไฟล์ฐานข้อมูล (Data File) - เก็บข้อมูลที่เราต้องการ
• ไฟล์บันทึกการทำงาน (คำมันอาจจะแปลกๆ เปลี่ยนเป็น Log File ดีกว่า) - มันเป็นความพิเศษทางคณิตศาสตร์ที่สามารถทุก Step ของการทำงาน เพื่อเอาให้ใช้ในกรณีที่เกิดปัญหาขึ้นกับไฟล์ฐานข้อมูล ติดตามดูอดีตของมันนะครับ

คราวนี้มาดูที่ตัวของ MS SQL บ้างดีกว่า ว่าไฟล์กลุ่มนี้มีอะไรบ้าง

• ไฟล์ฐานข้อมูล (Data File)  - มี 2 แบบ คือ Primary Data File (.mdf) และ Secondary Data File (.ndf) แต่ส่วนใหญ่ เรามักคุ้นเคยกับไฟล์ mdf มากกว่าครับ
• ไฟล์บันทึกการทำงาน (LogFile)  - ในที่นี้เก็บเป็นไฟล์ Log Data File (.ldf) ครับ

จากหัวเรื่อง Blog นี้จั่วหัวในเรื่องของ Database Log File ไว้ เรามาดูกันดีกว่ามันมีการทำงานอย่างไร เริ่ม Database ทำงาน

• Operation เข้าปรับให้ Database ให้ใหม่ที่สุด (กลุ่ม INSERT READ DELETE)
• Operation ที่ต้องยุ่งกับโครงสร้างของ Database (กลุ่ม DDL พวก Create Table / Index เป็นต้น)
• Operation ที่เราจัดการกับ DBMS โดยตรง เช่น Start/Stop Database เป็นต้น

รู้แล้วว่าอะไร ที่มันต้อง Log บ้าง คราวนี้ลองมาดูโครงสร้างแบบ Logical(Virtual Log) ของ Log กัน โดย Log แต่ละตัวมี ID ประจำของมัน ก็ คือ  Log Sequence Number (LSN) ซึ่งเรียงกันไปเรื่อยๆ ดังรูป

โดย Log มันยังไม่ได้ลง Disk ที่เป็น Physical ทันทีนะครับ มันถูกเก็บลงใน Buffer เพื่อพักข้อมูลไว้ก่อน เพราะถ้าเอาลง Disk เลยจะเจอปัญหา I/O ได้ โดยจะรอจนถึง เงื่อนไขที่กำหนด ว่าควรย้ายจาก Buffer ลง Disk เราเรียกจุดนี้ว่าเป็น Check Point ครับ ซึ่ง Check Point ของเจ้าตัว SQL Server มีหลายแบบ ผมติต่างเป็นแบบ Automate Check Point (ง่ายดี)

แล้ว Log พวกนี้มันช่วยเราในการ Recovery อย่างไรหละ ตอนนี้ต้องรู้คำศัพท์หลายคำเลย

• MinLSN : Minimum Log Sequence Number เป็นจุดที่เริ่มต้นสำหรับใช้ในการ Recovery ครับ หรือใช้ในการ Truncate Log ครับ
• Active Log : กลุ่มของ Log File ตั้งแต่ MinLSN ไปจนถึง Log อันล่าสุดที่เพิ่งเขียนไป

จากรูปนี้ เรามาลองไล่กันดีว่า ถ้ามีสถานการณ์ตัวอย่าง เอาจากรูปที่นะครับ อะไร คือ MinLSN ถ้าระบบมีปัญหา พบว่าระบบมีการทำ Check Point ที่ LSN 130 สิ่งที่เราต้องกลับไปดู คือ

• ว่ามีกี่ Transaction และ ทำงานสมบูรณ์ หรือไม่
  - คำตอบ ไม่ จาก LOG ที่ได้มามี Transaction T1 และ T2 ครับ โดย T1 ถูก Commit สมบูรณ์แล้ว แต่ T2 งานยังไม่เสร์จครับ
• แล้วเราควรเริ่มใช้ LSN อันไหนในการ Recovery หละ
  - คำตอบ ต้องให้ทำให้ Transaction ที่ยังไม่ถูก Commit ทำให้งานเสร็จก่อนครับ เรารู้แล้วว่า T2 มีปัญหา เราต้องย้อนกลับไปที่ที่ T2 เริ่มต้นครับ จากรูปลองไล่ดูแล้วพบว่า LSN124 ครับ ตัว ?MinLSN คือ 124

หลังจากที่เราเข้าใจวิธีการเขียน Log แล้ว มาดูกันว่าตัว MS SQL Server เปิด Option อะไรให้เราบ้าง Recovery Model ซึ่งมันมีผลกับการเขียน Log ครับ

• Simple Recovery Model - ประหยัดพื้นที่ดิสก์
  • เมื่อถึงจุด Checkpoint ตัวระบบเคลียร์พื้นทีของ Record แรกสุด(MinLSN) เป็นที่ว่างเขียนทับได้
  • หาก Database มีปัญหาต้อง Restore จาก Backup File เท่านั้น ไม่สามารถเอา Transaction Log มาใช้ได้ ยกเว้นกรณีที่ช่วงเวลาที่มีปัญหาเกิดขึ้น ตัว DBMS ยังไม่ถึงจุด Check Point ของการ Clear Log
• Bulk logged Recovery Model - เก็บเพิ่มขึ้นมาจากแบบ Simple
  • เก็บ Log ทุกๆอย่าง ยกเว้นงานลักษณะชุดใหญ่ (Bulk Operation) เช่น SELECT INTO, BULK INSERT, CREATE INDEX  and TEXT/IMAGE
  • หาก Database มีปัญหาสามารถใช้ Transaction Logs มากู้ได้
  • Transaction Logs มีขนาดใหญ่ ต้องทำการ Backup สม่ำเสมอ เพื่อป้องกันไม่ให้ Logs File โตเกินไป
• Full Recovery Model - เก็บทุกอย่างทั้งหมด
  • เก็บ Log ในทุกๆกรณี หาก Database มีปัญหาสามารถใช้ Transaction Logs
  • เนื้องจากเก็บทุกๆอย่าง ทำให้สามารถทำ Point-in-time recovery เลือกจุดที่กู้คืนข้อมูล ณ เวลาที่สนใจได้ เช่น 23-APR-2012 เวลา 18.25 Database มีปัญหา เราสามารถเลือกกู้คืน(Recovery) ไปสถานะเมื่อเวลา 18:20 ได้เลย
  • ห้ามเขียนทับจนกว่า มีการทำ Manual Transaction Log Backup

มันมี Recovery Model มา เราต้องเลือกวิธีการที่เหมาะสมด้วยนะครับ ถ้าเลือกผิด Database เราที่ดู มักเจอปัญหาว่า ไฟล์ Log (.ldf) ใหญ่กว่าไฟล์ฐานข้อมูล (.mdf หรือ .ndf) อีก มาดูการแก้ไขได้ใน Blog นี้ครับฺ (Blog การแก้ไขนี้ดองยาวนานมาก เริ่มเขียนตั้งแต่ปี 2014 ยุ่งๆมาเรื่อยๆ จนมาปิดได้ในปี 2017)

Reference

• Database Checkpoints (SQL Server)
• Transaction Log Physical Architecture