MSSQL: Reduce log file of SQL Server

(Anhgolden's Blog) - Dung lượng file SQL DB ngày càng tăng, trong khi dữ liệu chỉ có vài GB, nhưng file Log thì hàng trăm GB (gấp nhiều lần file dữ liệu).

Giả sử DB là Dbname, sẽ có 2 file:
- File data: Dbname.mdf
- File log: Dbname_log.ldf

Nay muốn giảm dung lượng file log (Dbname_log.ldf), xin chia sẻ dùng Sql Script như sau:

===
USE Dbname;
GO

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

Kết quả sau khi thực hiện:

==
Diễn giải:

B1: SET RECOVERY SIMPLE
Có 3 chế độ Recovery trong SQL Server: FULL, SIMPLE và BULK LOGGED

Chế độ mặc định là FULL.

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.

SET RECOVERY 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ỏ.

B2: DBCC SHRINKFILE (Dbname_Log, 1)

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ể.

B3: Sau đó SET RECOVERY về lại FULL

(Source: Sưu tầm)