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

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

.ldf เป็นไฟล์ที่แยกเก็บบันทึก transaction ของไฟล์ฐานข้อมูล (.mdf) และจำเป็นต้องใช้งานคู่กัน ถ้าหา 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

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

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

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

1.ลบ หรือ เปลี่ยนชื่อ ไฟล์ ldf เป็นชื่ออื่น
2.เลือก 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)

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 เป็นประจำนั่นเอง

อ้างอิงจาก :http://x-automation.blogspot.com
เรียบเรียงโดย : http://www.treranan.com