Giảm Bớt Dung Lượng File LOG Của SQL Server

Vào một ngày đẹp trời, bạn nhận thấy rằng file LOG của mình quá lớn, chiếm gần hết ổ cứng và không thể thực hiện bất kì một thao tác nào trên dữ liệu.
Hay bạn thấy, trong khi dữ liệu của mình chỉ có vài GB, mà file LOG lên đến tận hàng trăm GB.
Phải làm thế nào ?

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 DB
Tuy nhiên với CSDL đòi hỏi tính sẵn sàng cao, thì ko mấy ai cho phép bạn làm điều này.

- Backup LOG với OpTION là TRUNCATE_ONLY hoặc NO_LOG
Với phiên bản SQL Server 2008 thì đã bỏ Option này

SQL Server 2005 Books Online:

“[TRUNCATE_ONLY]

This option will be removed in a future version of SQL Server. Avoid using it in new development work, and plan to modify applications that currently use it.

…

We recommend that you never use NO_LOG or TRUNCATE_ONLY to manually truncate the transaction log…”.

Tôi thường dùng cách thứ 3.

Giả sử DB của tôi là Test.

File Data : Test_Data.MDF
File Log : Test_Log.LDF

USE Test;
GO

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

GO

Giải thích

- 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 DB, 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 DB ở 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 DB lớn, có kế hoạch Backup riêng, bạn cần hỏi ý kiến của DBA trước khi thực hiện. Vì một vài chế độ BackUp dựa rất nhiều vào file LOG.

Tuy nhiên nếu có DBA, thì không bao giờ để xảy ra trường hợp này, vì công việc của họ là thường xuyên theo dõi, giám sát và xử lý để Server hoạt động tốt.

http://www.sqlviet.com/blog/lam-sao-giam-bot-dung-luong-file-log-cua-sql-server

Xóa Log File trong SQL Server 2005

How to Truncate Log File in SQL Server 2005 – Giảm dung lượng Log File trong SQL Server 2005

SQL Server 2005 có khá nhiều điểm khác biệt với SQL Server 2000. Một trong những điểm đó là việc thu nhỏ dung lượng file Log (*.LDF)

Trong SQL 2000, bạn chỉ cần Shrink với bất cứ kích thước nào bạn muốn. Còn với SQL Server 2005, đôi khi bạn không thể Shrink được dữ liệu.

Bài viết này cPanel.vn hướng dẫn các bạn cách làm giảm dung lượng file Log của SQL. Môi trường làm việc là Microsoft SQL Server Management Studio.

Cách 1: Sử dụng tính năng shrink

Chuột phải vào database bạn cần Shrink, chọn Tasks, chọn tiếp Shrink và cuối cùng chọn Files.
Bạn tick vào Reorganize pages before releasing unused space và set dung lượng file. Bạn có thể để 1M

Shrink Database MSSQL 2005

Cách 2: Xóa file log.

Đôi khi, bạn làm theo cách 1 sẽ không được. Bản thân người viết cũng đã nhiều lần gặp trường hợp không shrink được database. File log quá lớn.
Cách dưới đây sẽ giúp bạn xử lý vấn đề này, lấy lại phần ổ cứng bị chiếm dụng một cách phí phạm.

1. Để an toàn, luôn nên backup database trước khi bạn làm bất cứ thao tác gì.
2. Chuột phải vào database cần xử lý, chọn Tasks –> Detach.
3. Mở folder chứa log file của database (file .LDF), rename nó đi. Ví dụ: cpanel_web.LDF đổi thành cpanel_web_backup.LDF
4. Tiến hành Attach lại database của bạn. Khi add file .MDF, nó sẽ tự động lấy luôn file LDF đi kèm. Bạn chỉ cần bấm chuột vào dòng file LDF đó và bấm nút Remove. Hệ thống sẽ tự tạo lại cho bạn file LDF mới.
5. Cuối cùng, kiểm tra lại dữ liệu của bạn. Nếu mọi việc ổn rồi bạn có thể xóa file LDF cũ đi

Cách trên hy vọng sẽ giúp bạn loại bỏ các file LDF dung lượng lớn, lấy lại ổ cứng cho bạn.
Chúc các bạn thành công.

http://www.cpanel.vn/2011/07/21/database-server/mssql/how-to-truncate-log-file-in-sql-server-2005-giam-dung-luong-log-file-trong-sql-server-2005