Các cách tối ưu dung lượng Log file của database SQL Server

Nguyễn Thành Luân 4 phút đọc

Đã có khá nhiều trường hợp, Khách hàng tìm đến chúng tôi để nhờ hỗ trợ kiểm tra xem sao mà nhận được thông báo rằng Web-hosting đang sử dụng đã bị tạm dừng hoạt động với lý do đầy dung lượng mặc dù trong suốt thời gian dài họ không hề thực hiện cập nhật, bổ sung thêm dữ liệu.

Sau khi phối hợp với Khách hàng chúng tôi nhận thấy rằng database SQL Server trên Web-hosting chiếm gần hết dung lượng, trong khi dữ liệu của họ thì chỉ có vài trăm MB mà file LOG lên đến tận vài chục GB. Chúng ta phải làm thế nào để tối ưu dung lượng của Log file này ?

Có rất nhiều cách để giải quyết vấn đề này

Detach DB, xóa file LOG, sau đấy ATTACH lại. Tuy nhiên với CSDL đòi hỏi tính sẵn sàng cao thì cách này không khả thi.
Backup LOG với OpTION là TRUNCATE_ONLY hoặc NO_LOG. Tuy nhiên với phiên bản SQL Server 2008 thì đã bỏ Option này.
Chúng tôi thường khuyến khích Khách hàng thực hiện SHRINK file log, giả sử chúng ta có file Data: MB_Data.MDF và file Log: MB_Log.LDF
1    USE MB;
2    GO
3    — Truncate the log by changing the database recovery model to SIMPLE.
4    ALTER DATABASE MB
5    SET RECOVERY SIMPLE;
6    GO
7    — Shrink the truncated log file to 1 Mb.
8    DBCC SHRINKFILE (MB_Log, 1);
9    GO
10    — Reset the database recovery model.
11    ALTER DATABASE MB
12    SET RECOVERY FULL;
13    GO

Bạn có thể hiểu phương pháp này theo cách giải thích sau :

Có 3 chế độ Recovery trong SQL Server: FULL, SIMPLE và BULK LOGGED. Chế độ mặc định là FULL. Bạn có thể vào phần Option của database, xem trong Recovery Model. Khi ở chế độ này, bất kì một transaction nào, kể cả khi đã commit cũng đều được lưu trong LOG, do đó có thể dựa vào những Transaction này để “quay lui (rollback)” DB về bất kì thời điểm nào. Vì thế với những DB có Transaction nhiều, DATA ít thì file LOG vẫn có thể rất lớn.
Đầu tiên SET RECOVERY của DB về SIMPLE, ở chế độ này sau khi Transaction được COMMIT, sẽ tự động xóa. Do vậy File LOG của database ở chế độ này thường rất nhỏ.
Dùng DBCC SHRINKFILE để SHRINK file log xuống còn 1 Mb. Nếu không set Recovery về SIMPLE, thì sẽ ko thể xóa bỏ hết các Transaction đã được COMMIT. SHRINKFILE chỉ thu dọn và sắp xếp và phân bố lại dữ liệu, bỏ các vùng trống để giải phóng bộ nhớ, chứ không phải xóa dữ liệu. Vì thế ở chế độ FULL, SHRINKFILE hầu như ko tác dụng, hoặc nếu có thì file LOG dung lượng giảm đi ko đáng kể.
Sau đó SET RECOVERY về lại FULL. Trên MSDN cũng khuyên nếu muốn Backup LOG, các bạn nên chuyển về chế độ SIMPLE, hơn là backup LOG với Truncate_Only và No_LOG.

Chú ý: Với những database dung lượng lớn, bạn cần phải chủ động backup database trước và tham khảo ý kiến của lập trình website trước khi thực hiện.

Gửi phản hồi về bài viết này