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

หลายๆพอใช้ Database ไป ผ่านไปนานเข้า ระบบฟ้องว่า Disk เต็ม พอเข้าไปดูอ้าวไฟล์ Log (.ldf) มันโตกว่าไฟล์ Database (.mdf) เสียอีก คราวนี้มาลองดูสาเหตุกัน

สาเหตุที่ Database Log File โตไม่หยุด

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

พอรู้สาเหตุแล้ว มาดูวิธีแก้บ้าง

- ปรับเปลี่ยนเลือก Recovery Mode ที่ใช้ครับ

ลองพิจารณา Recovery Model จาก Blog ผม หรือจะถาม Google ก็ได้ครับ เมื่อได้ Recovery Model ที่ใช่แล้ว มาดูวิธีขั้นตอนการปรับแก้ครับ

• เข้าไปที่ SQL Server Management Studio (SSMS) อย่างน้อยเป็น Windows Authentication หรือใช้ User ที่มีสิทธินการจัดการ DB ครับ
• ในหน้าจอ Object Explorer browse เลือก Database
• คลิกขวาที่ Database ที่เลือก >> Properties >> Options
• หาในส่วนของ Recovery Model เลือกให้เหมาะสมกับการใช้งาน เคสนี้ ถ้าจะลดขนาด .ldf ต้องปรับมาเป็นแบบ Simple แต่ถ้าปรับไปแล้ว มันจะมีข้อจำกัด Point-in-Time Recovery ถ้าทำพวก Log Shipping หรือ Database Mirroring ที่ต้องใช้ Transaction Log มี Impact ด้วยนะ วางแผนดีก่อน
• กด OK

- ลดขนาดของ Database Log File (.ldf)

Option1: Shrink Database ตามวิธีการ

• เข้าไปที่ SQL Server Management Studio (SSMS)
• ในหน้าจอ Object Explorer browse เลือก Database
• คลิกขวาที่ Database ที่เลือก Tasks >> Shrink >> Files
• ดู File Type = Log
• Shrink action ดูว่่าให้ DB มัน คืนพื้นที่ที่ไม่ได้ใช้ (Release unused space)
• กด OK

Option2: Detach/Attach - เพื่อให้มัน สร้าง .ldf log file ใหม่ (ควรทำ Full Backup ก่อนนะ)

• เข้าไปที่ SQL Server Management Studio (SSMS)
• ในหน้าจอ Object Explorer browse เลือก Database
• คลิกขวาที่ Database ที่เลือก Tasks >> Detach
• ถ้ายังมี Application หรือ User ใช้งาน Database ก้อนนี้ออยู่ มันมีหน้าจอให้เตะ (Drop Connection) ครับ
• ลบ หรือ เปลี่ยนชื่อ ไฟล์ .ldf เป็นชื่ออื่น
• Attach Database (.mdf) กลับเข้าไปใหม่ โดยคลิกขวาที่ Database ที่เลือก Tasks >> Attach

Option3: Manual Log Backup

• เข้าไปที่ SQL Server Management Studio (SSMS)
• ในหน้าจอ Object Explorer browse เลือก Database
• คลิกขวาที่ Database ที่เลือก Tasks >> Backup หน้าจอ Backup แสดงขึ้นมา
• ตรวจสอบชื่อ Database (จะเปลี่ยนเป็นก้อนอื่นก็ได้นะ)
• ตรวจสอบ Recovery model
• Backup type เลือก Transaction Log ส่วนของ Option มีหลายแบบครับ เช่น Full(ทั้งหมด), Difference(เอาเฉพาะส่วนที่เปลี่ยนแปลง) เป็นต้น
• จากนั้นเป็นการใส่ข้อมูลทั้งไป เช่น พวกชื่อ Backup / คำอธิบาย และมีการ Config ด้าน Security
• เลือก Destination
• รอ Vertify
• จากนั้นกำหนด Config ของ Transaction Log ว่าให้ตัด Truncate Log หลัง Backup ไหม
• มีหน้าจอถามไปอีก จากนั้นรอครับ

Ref: https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-a-transaction-log-sql-server?view=sql-server-ver17

ในทีสุดเขียน Blog ที่ดองมา 3 ปีเสร็จ ช่วงนี้ยังมีเวลาว่างๆ ผมพยายามเก็บ Blog เก่าๆที่เขียนค้างไว้ หรือเขียน Blog ใหม่ไปเรื่อยๆ ครับ

อ่อแล้วถ้าจะลบตัวเนื่อ data mdf / ndf ลองมาดู Blog นี้ได้ครับ [MSSQL] มาดูขนาดของ Database และ Table กัล