ในการใช้งานฐานข้อมูล MS SQL Server จะมีไฟล์สำคัญ 2 ประเภท คือ

1. Primary Data File (.mdf)
2. Transaction Log File (ldf)

Primary Data File (.mdf)

Primary Data File (.mdf) คือไฟล์ข้อมูลหลักที่เก็บในฐานข้อมูล MS SQL Server ไฟล์นี้จะเพิ่มขนาดขึ้นตาม record ที่มีการ insert เข้าตารางในฐานข้อมูล

Transaction Log File

Transaction Log File (.ldf) เป็นไฟล์ที่เก็บประวัติคำสั่ง SQL ที่เคยดำเนินการกับฐานข้อมูล ซึ่งจะถูกนำไปใช้ได้ในหลายขั้นตอน เช่น

• การ rollback คำสั่ง transaction
• การ backup ฐานข้อมูล
• การทำ replication ข้อมูลไปเชื่อมกับฐานข้อมูลแบบ Cluster

โดยปกติเมื่อสร้างฐานข้อมูลขึ้นค่าเริ่มต้น ขนาดไฟล์ทั้งสองจะสามารถเพิ่มขึ้นได้ไม่จำกัดตามปริมาณการใช้งาน

หากการใช้งานฐานข้อมูลมี transaction เกิดขึ้นจำนวนมากและไม่ได้มีตรวจสอบบำรุงรักษาฐานข้อมูลที่ถูกต้องจะทำให้ขนาดไฟล์ฐานข้อมูล (.mdf) และ transaction log(.ldf) มีขนาดโตเกินกว่าความเป็นจริงได้ จนทำให้มีโอกาส disk เต็มจนไม่สามารถใช้งานฐานข้อมูลได้

จากตัวอย่างภาพฐานข้อมูลด้านบน

• ไฟล์ .mdf มีขนาด 2.44 GB (2,504 MB)
• ไฟล์ .ldf มีขนาด 25.3 GB (25,800 MB)

สังเกตว่าไฟล์ Transaction log (.ldf) มีขนาดโตกว่าไฟล์ข้อมูลจริงของฐานข้อมูลถ้าหากมี transaction เกิดขึ้นในระบบจำนวนมาก ไฟล์ LDF จะถูกปรับขนาดลงอัตโนมัติเมื่อการการ backup ฐานข้อมูลเป็นประจำ แต่หากเราต้องการลดขนาดไฟล์ LDF สามารถทำได้ด้วยขั้นตอน ดังต่อไปนี้

1. กำหนด Recovery Model ฐานข้อมูลเป็นแบบ Simple
2. Run คำสั่ง Checkpoint
3. ทำการสำรองฐานข้อมูลแบบ Full Backup
4. ทำการ Shrink Database
5. กำหนด Recovery Model ฐานข้อมูลกลับเป็นแบบ Full หรือแบบที่ตั้งไว้ก่อนหน้า
6. ทำการกำหนด Maximum File Size สำหรับ ไฟล์ LDF

1. กำหนด Recovery Model ฐานข้อมูลเป็นแบบ Simple

คลิกขวากที่ฐานข้อมูลที่ต้องการ เลือก Properties

ที่ Page General เลือก Recovery Model เป็นแบบ Simple

2. Run คำสั่ง Checkpoint

3. ทำการสำรองฐานข้อมูลแบบ Full Backup

คลิกขวาที่ฐานข้อมูล เลือก Tasks => Back Up...

ที่ Page General เลือก Backup Type เป็นแบบ Full

4. ทำการ Shrink Database

คลิกขวาที่ฐานข้อมูล เลือก Tasks => Shrink => Database คลิก OK

5. กำหนด Recovery Model ฐานข้อมูลกลับเป็นแบบ Full หรือแบบที่ตั้งไว้ก่อนหน้า

คลิกขวาที่ฐานข้อมูล เลือก Properties ที่ Page General เลือก Recovery Model เป็นแบบ Full หรือแบบที่เลือกไว้ก่อนหน้า

6. ทำการกำหนด Maximum File Size สำหรับ ไฟล์ LDF

ที่ Page Files ในตาราง Database files เลือกไฟล์ transaction log ในคอลัมน์ Autogrowth / Maxsize ในหน้าต่าง Maximum File Size เปลี่ยนจาก Unlimited เป็น Limited to (MB) ตามขนาดไฟล์ที่ต้องการจำกัด

ขนาดไฟล์ Transaction Log (LDF) ของฐานข้อมูลถูกลดขนาดลงหลังจาก ทำขั้นตอน Shrink database เรียบร้อย

ทั้งนี้เพื่อป้องกันไฟล์ Transaction Log มีขนาดโตจนเกิดพื้นที่จัดเก็บอีก ผู้ดูแลฐานข้อมูลควรตั้ง Schedule ทำการสำรองฐานข้อมูลอย่างสม่ำเสมอเพื่อให้เกิดกระบวนการเคลียร์ข้อมูลใน transaction log โดยอัตโนมัติ