แก้ปัญหา Database Log File โตไม่หยุด

ปัญหาไฟล์ Log ของฐานข้อมูล SQL Server ที่ขยายตัวอย่างไม่หยุดหย่อนนั้นมักเกิดจากการจัดการทรานเซ็กชันและการบำรุงรักษาไฟล์ Log ที่ไม่เหมาะสม พอใช้ Database SQL Server ผ่านไปนานๆเข้า ระบบฟ้องว่า Disk เต็ม พอเข้าไปดูอ้าวไฟล์ Log (.ldf) มันโตกว่าไฟล์ Database (.mdf) เสียอีก คราวนี้มาลองดูสาเหตุกัน ต่อไปนี้คือขั้นตอนและแนวทางในการจัดการกับปัญหานี้

สาเหตุที่เป็นไปได้

• เลือก Recovery Model ที่ไม่เหมาะสม
• ไม่มีการจัดเก็บ Backup Log ออกมา พอไปตั้งให้ Log มัน Auto Grow แล้วมันเลยบวมครับ

1. ตั้งค่า Recovery Model ของ Database

วิธีแก้ปัญหาขนาดไฟล์ .LDF

ตั้งค่า Recovery Model ของ Database เป็น simple เพื่อให้ Database หยุดขยายขนาด log แบบไม่มี limit

ใช้ SQL Server Management Studio login เข้าไปจัดการเซอฟเวอร์ฐานข้อมูล

ใน Object Explorer browse ไปที่ไฟล์ database ที่ต้องการ Right click > Properties > Options เลือก Rocovery model : Simple

2. ลดขนาดของ log file (.ldf) ที่เกินจำเป็น

Options A: Shink database

เลือกคลิ้กขวาที่ database > Tasks > Shrink > Files

File type: Log

Shrink action : Release unused space

กด OK

Option B: detach/attach สร้าง .ldf log file ใหม่ *ควรจะทำ full backup ไว้ก่อน

เลือกคลิ้กขวาที่ database > Tasks > Detach...

ถ้ามีการใช้งาน database นี้อยู่จะมี Active connections ให้ปิดโปรแกรมที่ใช้งานก่อนติ๊กถูกที่ drop connections กด OK

ลบ หรือ เปลี่ยนชื่อ ไฟล์ ldf เป็นชื่ออื่น

เลือก Tasks > Attach ไฟล์ .mdf เข้าไปใหม่

Recovery model ใน SQL server database

Simple recovery model : เมื่อถึงจุด Checkpoint ระบบจะเคลียร์พื้นทีจาก Record แรกสุด - MinLSN เป็นที่ว่างเขียนทับได้

Full recovery model : records ทั้งหมดจะห้ามเขียนทับจนกว่าเราจะทำ transaction log backup (Manual)

Bulk-logged recovery model : จะสร้าง Checkpoint ทุกครั้งที่เรา backup log หรือทำ bulk-copy

สรุปว่า นอกจาก Simple Recovery model แล้วจะต้องมี admin คอยทำหน้าที่จัดการ backup log file และ Shrink database เป็นประจำนั่นเอง แต่ Size ของ *.mdf และ *.ldf จะเปลี่ยนแปลงหรือ date modified ก็ต่อเมื่อ SQL close file หรือขยาย size ของ Database หรือ restart service

สรุปเพิ่มเติมเพื่อแก้ปัญหาระยะยาว..

1.ตรวจสอบโหมดการกู้คืน (Recovery Model) ของฐานข้อมูล: หากฐานข้อมูลของคุณตั้งค่าเป็นโหมดการกู้คืนแบบ Full หรือ Bulk-Logged, ไฟล์ Log จะเติบโตอย่างรวดเร็วเนื่องจากทุกการเปลี่ยนแปลงจะถูกบันทึก หากไม่จำเป็นต้องใช้การกู้คืนระดับการทำธุรกรรม คุณสามารถเปลี่ยนเป็นโหมด Simple เพื่อลดขนาดไฟล์ Log

2.ทำการ Backup Log อย่างสม่ำเสมอ: ในโหมด Full หรือ Bulk-Logged, จำเป็นต้องทำการ Backup Log อย่างสม่ำเสมอเพื่อปลดปล่อยพื้นที่ในไฟล์ Log และป้องกันไม่ให้มันเติบโตมากเกินไป

3.จำกัดขนาดสูงสุดของไฟล์ Log: ตั้งค่าขนาดสูงสุดของไฟล์ Log เพื่อหลีกเลี่ยงการเติบโตอย่างไม่มีขอบเขต

4.ตรวจสอบและแก้ไขปัญหาที่ทำให้เกิดการ Lock ยาวนาน: การทรานเซ็กชันที่ไม่ได้รับการประมวลผลเสร็จสิ้นสามารถทำให้ Log ขยายตัวได้ ควรตรวจสอบและแก้ไขปัญหาเหล่านี้

5.ใช้ DBCC SHRINKFILE เพื่อย่อขนาดไฟล์ Log: หลังจากทำการ Backup Log, คุณสามารถใช้คำสั่ง DBCC SHRINKFILE เพื่อย่อขนาดไฟล์ Log แต่ไม่ควรใช้คำสั่งนี้บ่อยเนื่องจากอาจทำให้เกิดการ Fragmentation

6.ตรวจสอบและปรับปรุงการออกแบบฐานข้อมูลและแอพพลิเคชัน: อาจมีปัญหาในการออกแบบฐานข้อมูลหรือแอพพลิเคชันที่ทำให้เกิดการเขียน Log มากเกินไป

7.ใช้งาน Maintenance Plans: สร้างและใช้งาน Maintenance Plans เพื่อจัดการกับการ Backup และการดูแลรักษาไฟล์ Log อย่างอัตโนมัติ

8.ตรวจสอบปัญหาอื่นๆ: ใช้เครื่องมืออย่าง SQL Server Profiler หรือ Dynamic Management Views เพื่อตรวจสอบและค้นหาปัญหาที่อาจเกิดขึ้น

การดำเนินการเหล่านี้ควรช่วยลดปัญหาของไฟล์ Log ที่เติบโตอย่างไม่หยุดหย่อนใน SQL Server ได้ แต่ต้องจำไว้ว่าการบำรุงรักษาที่ดีและการจัดการทรานเซ็กชันอย่างมีประสิทธิภาพเป็นสิ่งจำเป็นในการป้องกันปัญหาในระยะยาว หวังว่าคงได้คำตอบที่ตามหา..@m1n