thucnguyenblog logo

Cách tối ưu logfile của SQL Server

thucnguyenit35
SQL
24/10/2017 - 14:36:00

Với những ai đang làm việc với SQL Server chắc chắn sẽ có lúc gặp tình trạng dung lượng của file database rất lớn mà trong khi đó số lượng bản ghi và các bảng trong database cũng không nhiều. Dung lượng file database lớn phần nào ảnh hưởng đến tốc độ truy xuất dữ liệu và chúng còn chiếm hết không gian của ổ đĩa.

Database SQL Server trên Web-hosting chiếm gần hết dung lượng, trong khi dữ liệu 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
  1. 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.
  2. 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.
  3. 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: dbBlog_Data.MDF và file Log: dbBlog_Log.LDF


USE dbBlog;
GO 
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE dbBlog
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 Mb.
DBCC SHRINKFILE (dbBlog_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE dbBlog
SET RECOVERY FULL;
GO

Giải thích:

  • Có 3 chế độ Recovery trong SQL Server: FULLSIMPLE 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ế độ FULLSHRINKFILE 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.

Lưu ý: Bài viết này chỉ mang tính tham khảo. Để đảm bảo an toàn cho dữ liệu của bạn, việc shrink file log nên được thực hiện vào lúc có ít kết nối đến hệ thống và bạn nên cẩn thận backup lại database của mình trước khi thực hiện việc này


Tags: SQL
 Author infomation:
Xin chào các bạn! Tôi tên là Thức, tôi sinh ra và lớn lên tại quê hương Yên Mỹ, Yên Mô, Ninh Bình, từ thời Trung học tôi đã yêu thích tin học và công nghệ. Tôi tốt nghiệp ngành Công nghệ thông tin tại Đại học Mở Hà Nội, hiện tại tôi đang là một Web Developer. Tôi tạo ra blog này nhằm mục đích chia sẻ kinh nghiệm, kiến thức mà tôi đã trải qua trong cuộc sống, vì thế sẽ có thể thiếu sót mong các bạn đóng góp để giúp tôi hoàn thiện hơn.