Cách giảm dung lượng file transaction log trong SQL Server

Kiến trúc file CSDL SQL

Khi tạo một CSDL trong SQL Server, tự động sẽ sinh ra hai tệp là tệp đuôi .MDF và .LDF
Thí dụ mình tạo một CSDL tên là Demo thì sẽ sinh ra: Demo.mdf và Demo_log.ldf

Khi để chế độ Recovery Model mặc định là Full hoặc Bulk-logged thì sau một thời gian sử dụng, dung lượng file log (.ldf) sẽ phình lên rất nhiều, có thể dung lượng sẽ file log sẽ gấp nhiều lần file data. Bạn có thể chuột phải vào CSDL và chọn properties để xem

Với dung lượng file log lớn như thế này vừa tốn dung lượng ổ cứng HDD, vừa làm chậm thao tác trên CSDL đó. Vì vậy chúng ta phải giảm dung lượng file log đi là điều tất yếu.

Cách giảm dung lượng file log
Cách 1. Sử dụng SQL management tool để xóa log

Cách 2. Xóa log bằng cách sử dụng câu lệnh SHRINKFILE

ALTER DATABASE [mydatabase] SET RECOVERY SIMPLE 
DBCC SHRINKFILE(<log_file_name_Log>)
ALTER DATABASE [mydatabase] SET RECOVERY FULL

Thứ tự thực hiện câu lệnh trên như sau:

• B1. Chuyển CSDL về simple model, thì việc xóa log mới có hiệu quả.
• B2. Chạy lệnh xóa log file
• B3. Chuyển về Recovery Full Model để CSDL có thể thực hiện log các trạng thái

KẾT QUẢ
Sau khi thực hiện một trong hai cách trên thì dung lượng của file log của mình giảm xuống đang ngạc nhiên

PS: Các bạn nếu thấy hay thì share giúp nhé hoặc để lại comment.

XEM TIẾP

Cách phân biệt giữa các loại Recovery Model trong MSSQL Server

Bài này sẽ nói về cách phân biệt giữa các loại Recovery Model trong SQL. Cách sử dụng các Mode này sao cho hiệu quả.

1. Các loại Recovery Model

Hệ quản trị cơ MSSQL Server có 3 loại Recovery model:

1. Full, 
2. Simple 
3. Bulk-logged.

Khi chúng ta tạo một CSDL mới thì hệ thống tự động chọn model là: Full.

Chúng ta có thể sử dụng SQL management tool để xem như sau:
- Click chuột phải vào database, chọn Properties

- Trên cửa số thông tin về database, chọn tab Options

- Recovery Model: Full, Bulk-logged, Simple

Hoặc có thể dụng câu lệnh SQL để truy vấn

SELECT name,
       recovery_model_desc
FROM sys.databases GO

2. Cách sử dụng các loại Recovery Model

Full Recovery Model:

• Sao lưu gần như tất cả những thay đổi trong cơ sở dữ liệu. Nên việc bạn có thể phục hồi được tất cả dữ liệu.
• Nếu cơ sở dữ liệu có chứa nhiều nhóm tập tin, và bạn muốn khôi phục từng phần đọc / ghi filegroups thứ cấp và tùy chọn, chỉ đọc filegroups.
• Người quản trị có thể phục hồi được cơ sở dữ liệu đến trước thời điểm db bị hỏng hóc.
• Dung lượng file data và file log sẽ phình ra rất nhanh, và file log có thể lớn hơn rất nhiều so với file data. Dẫn đến chi phí cho phần cứng sẽ đội lên khủng khiếp.
• Và tất nhiên khi file phình lên thì truy vấn SQL sẽ chậm đi, thao tác với CSDL cũng chậm đi. Bạn phải thường xuyên xóa bớt file log để hệ thống chạy nhanh hơn.

Bulk-Logged Recovery Model:

• Gần như bạn có thể phục hồi mọi thứ nếu chọn Model này. Tương tự Full Recovery Model

Simple Recovery Model

• Không tự động lưu lại những sự thay đổi của CSDL. Nên có thể mất toàn bộ dữ liệu nếu người quản trị không backup thường xuyên.
• Do không lưu lại các thay đổi vào file log nên dung lượng file log sẽ rất nhỏ so với file data.
• Đây là lựa chọn hợp lý cho admin có túi tiền không được rủng rỉnh.

3. Lời khuyên
Nếu dữ liệu của bạn có thể mất mát tại một số thời điểm thì nên sử dụng Simple Recovery Model, với ưu điểm là dung lượng file data và log sẽ nhỏ, nên hệ thống sẽ chạy mượt mà hơn. Tất nhiên chúng ta có thể tự tạo ra SQL Job Backup tự động cho CSDL theo thời gian để tránh mất mát dữ liệu (ngày backup 2 lần chẳng hạn) các bạn có thể tham khảo tại đây.

Chúng ta có thể thay đổi Recovery Model bằng cách sử dụng SQL Management Tool hoặc sử dụng câu lệnh sau:

USE master;


ALTER DATABASE DEMO
SET RECOVERY FULL ;

Chúc các bạn thành công khi làm việc với SQL Server.

XEM TIẾP