ปัญหาขนาด database log file .ldf โตไม่หยุด MSSQL server

file .ldf  เป็นไฟล์ที่แยกเก็บบันทึก transaction  ของไฟล์ฐานข้อมูล (.mdf file) และจำเป็นต้องใช้งานคู่กัน  ถ้าหาก transaction log ไม่สมบูรณ์หรือเราจัดการผิดวิธี sql server จะเรียกใช้งาน database ก้อนนั้นไม่ได้เลย

สาเหตุที่ไฟล์ .LDF ขนาดใหญ่ขึ้นไม่หยุด

ปกติค่า default ของฐานข้อมูลใน SQL server จะอยู่ใน full recovery mode แปลว่าถ้าเราไม่ทำอะไรซักอย่างกับ database ก้อนนี้ ขนาดของไฟล์ .ldf ก็จะโตขึ้นเรื่อยๆ ทุกวันจนฮาร์ดดิสเต็มในที่สุด

ในการติดตั้ง SQL Server ไม่ได้มี options นี้ให้เราเลือก admin หลายคนกด next >> ไปไม่โดยไม่สนใจบางทีก็คิดไปเองว่า การติดตั้ง database ลงในเซอฟเวอร์เป็นหน้าที่ของผู้พ้ฒนาโปรแกรมน่าจะรู้เรื่องมั้ง (พลาดแล้ว..) 

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

1. ตั้งค่า 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 

1. เลือกคลิ้กขวาที่ database  > Tasks > Shrink > Files
2. File type: Log
3. Shrink action : Release unused space
4. กด OK

Option B: detach/attach สร้าง .ldf log file ใหม่ *ควรจะทำ full backup ไว้ก่อน
เลือกคลิ้กขวาที่ database  > Tasks > Detach...

1. ถ้ามีการใช้งาน database นี้อยู่จะมี Active connections ให้ปิดโปรแกรมที่ใช้งานก่อนติ๊กถูกที่ drop connections กด OK
2. ลบ หรือ เปลี่ยนชื่อ ไฟล์ ldf เป็นชื่ออื่น
3. เลือก Tasks > Attach ไฟล์ .mdf เข้าไปใหม่

การทำงานของ database transaction log file ใน Microsoft SQL server 

ใน SQL database จะบันทึก Transaction Log โดยเขียนข้อมูลทีละ record ลงในLog file (.ldf) แบบ Circular เหมือนม้วนเทป เมื่อเขียนจนถึงตำแหน่งสุดท้ายของไฟล์แล้ววนกลับไปที่จุดเริ่มต้นของพื้นที่ว่าง แต่ถ้าไม่มีที่ว่าง Log file ก็จะขยายใหญ่ขึ้นเรื่อยๆ

เมื่อเขียน record ไปเรื่อยๆ ระยะเวลานึง SQL server จะสร้าง "Automatic Checkpoint"  ขึ้นโดยอัตโนมัติรวมทั้งเวลา stop/start sql server instant, backup, alter database ระบบก็สร้าง Checkpoint ขึ้น

พอถึงจุด Checkpoint sql server จะเช็คหาจุดแรกสุดของ record ที่ยัง active อยู่ (Begin Transaction แล้วยังไม่ได้ Commit/Rollback)  ณ เวลานั้น เรียกว่าตำแหน่ง Minimun Recovery LSN (MinLSN)  

ในรูปเป็นตัวอย่างจาก MSDN checkpoint ล่าสุดอยู่ที่ 147 ในเวลานั้น transaction ล่าสุดที่ยังไม่ commit คือ Begin Tran2 ที่ตำแหน่ง 142 ถือว่าเป็น MinLSN

Recovery model ใน SQL server database

1. Simple recovery model : เมื่อถึงจุด Checkpoint ระบบจะเคลียร์พื้นทีจาก Record แรกสุด - MinLSN เป็นที่ว่างเขียนทับได้
2. Full recovery model : records ทั้งหมดจะห้ามเขียนทับจนกว่าเราจะทำ transaction log backup (Manual)
3. Bulk-logged recovery model : จะสร้าง Checkpoint ทุกครั้งที่เรา backup log หรือทำ bulk-copy

สรุปว่า นอกจาก Simple Recovery model แล้วจะต้องมี admin คอยทำหน้าที่จัดการ backup log file และ Shrink database เป็นประจำนั่นเอง

source : http://aifarfa.blogspot.com/2012/02/database-log-file-ldf-mssqlserver.html