Hướng dẫn theo dõi và quản lý log transaction SQL Server

Hướng dẫn theo dõi và quản lý log transaction SQL Server

Khi SQL Server thực hiện quá trình transaction sẽ được ghi lại và lưu vào file log transaction của SQL Server.

Vì vậy trong bài viết này , chúng ta sẽ xem cách theo dõi quá trình tăng trưởng của log transaction SQL Server và cách quản lý sự tăng trưởng đó.

1.Tăng trưởng log transaction trong SQL Server

Trong cơ sở dữ liệu SQL Server sẽ ghi lại log cho mọi hoạt động đơn lẽ trong cơ sở dữ liệu , bao gồm cả việc bắt đầu hoặc kết thúc quá trình transaction SQL  khi quá trình sửa đổi dữ liệu được thực hiện. Trong quá trình transaction thực hiện nhiều với các log được ghi lại quá mức , file log transaction SQL Server sẽ tăng lên nhanh chóng , khi đã chiếm hết dung lượng sẽ tạo ra lỗi số 9002.

2.Theo dõi và quản lý log transaction SQL Server

Với một người quản lý cơ sở dữ liệu cần chủ động tối ưu quá trình giám sát log SQL Server , tránh những sự cố nghiêm trọng gây ra khi log chiếm hết dung lượng vì vậy bạn có thể sử dụng một công cụ giám sát như System Center Operations Manager (SCOM), Với bộ đếm hiệu suất hoặc chỉ cần bạn tạo cảnh báo và thông báo qua mail hoặc sms khi đã đạt đến mức dung lượng mà bạn đã đặt cho file log transaction SQL Server.

sys.dm_db_log_space_usage là một dạng quản lý tự động , được sử dụng để trả về thông tin mức dung lượng được sử dụng của log transaction SQL Server . Qúa trình query dưới đây có thể sử dụng để kiểm tra phần trăm dung lượng trống cho log transaction SQL Server hiện tại :

Và kết quả như sau :

Nếu kết quả trả về từ quá trình query trước đó giảm xuống (dung lượng còn lại ) một mức xác định trước , Thì trước khi file log transaction SQL Server hết dung lượng trống , Hệ thống sẽ báo qua email , sms hoặc cuộc gọi dựa trên hệ thống công cụ giám sát được sử dụng trong hệ thống của bạn .

Khắc phục lỗi 9002: Tại sao Transaction Log đầy và cách xử lý triệt để

Trong quản trị cơ sở dữ liệu SQL Server, việc đối mặt với lỗi 9002 (The transaction log for database ‘XYZ’ is full) là một tình huống khẩn cấp. Khi lỗi này xảy ra, mọi tác động ghi dữ liệu (Insert, Update, Delete) đều bị đình trệ, khiến hệ thống của bạn rơi vào trạng thái “đóng băng” một phần hoặc toàn bộ.

Bài viết này không chỉ giúp bạn “cứu” hệ thống ngay lập tức mà còn đi sâu vào phân tích nguyên nhân gốc rễ: Tại sao Log vẫn đầy dù bạn đã thực hiện Backup?

1. Giải quyết sự cố lỗi 9002: Các bước xử lý nhanh để “cứu” hệ thống

Khi nhận được thông báo lỗi 9002, điều đầu tiên bạn cần làm là giữ bình tĩnh. Việc vội vàng thực hiện các câu lệnh xóa dữ liệu (Delete) lúc này chỉ làm tình hình tệ hơn, vì lệnh Delete bản chất là một Transaction và nó sẽ tiếp tục yêu cầu thêm không gian Log.

Dưới đây là quy trình xử lý nhanh theo thứ tự ưu tiên:

Bước 1: Kiểm tra dung lượng ổ đĩa (Disk Space)

Trước tiên, hãy kiểm tra xem ổ đĩa chứa file .ldf còn dung lượng trống hay không.

• Nếu ổ đĩa đầy, hãy thử mở rộng dung lượng ổ đĩa hoặc di chuyển các file không liên quan sang ổ khác để nhường chỗ cho Log tiếp tục tăng trưởng.
• Kiểm tra thiết lập Autogrowth của file Log. Nếu bị giới hạn (Maxsize), hãy tạm thời nới lỏng giới hạn này.

Bước 2: Thực hiện Transaction Log Backup (Đối với Full/Bulk-Logged Recovery Model)

Đây là cách “chính thống” nhất để giải phóng không gian bên trong file Log mà không làm mất chuỗi Log (Log Chain). Khi bạn Backup Log, SQL Server sẽ đánh dấu các phần dữ liệu đã được sao lưu là “có thể ghi đè” (Reusable).

SQL

BACKUP LOG [Ten_Database] TO DISK = ‘D:\Backup\Ten_DB_Log.trn’

Bước 3: Chuyển sang Simple Recovery Mode (Chỉ dùng khi khẩn cấp)

Nếu bạn không cần giữ chuỗi Log để phục hồi dữ liệu tại một thời điểm cụ thể (Point-in-time recovery), việc chuyển tạm thời sang chế độ Simple sẽ buộc SQL Server tự động cắt cụt (Truncate) Log sau mỗi Checkpoint.

Lưu ý: Hành động này sẽ phá vỡ chuỗi Log Backup của bạn. Hãy cân nhắc kỹ trước khi thực hiện trên môi trường Production.

Bước 4: Thực hiện Shrink File (Co dãn file Log)

Sau khi đã Backup hoặc chuyển sang chế độ Simple, dung lượng “ảo” trong file Log đã được giải phóng, nhưng kích thước file vật lý trên ổ đĩa vẫn không đổi. Bạn cần lệnh Shrink để thu hồi dung lượng thực tế về hệ điều hành:

SQL

DBCC SHRINKFILE (Ten_Logical_File_Log, 100); — Thu nhỏ về 100MB

2. Nghịch lý: Tại sao Log không được cắt cụt (Truncate) dù đã Backup?

Nhiều quản trị viên thắc mắc: “Tôi đã thiết lập lịch Backup Log 15 phút một lần, nhưng file Log vẫn phình to và báo đầy?”

Thực tế, Backup Log chỉ là điều kiện cần, không phải điều kiện đủ. Việc cắt cụt Log (Log Truncation) chỉ diễn ra khi tất cả các bản ghi trong đó không còn cần thiết cho bất kỳ tính năng nào khác của SQL Server. Nếu có một tiến trình nào đó vẫn đang “giữ” bản ghi Log, SQL Server sẽ không thể đánh dấu vùng không gian đó là trống, dẫn đến việc file Log cứ thế lớn dần.

3. Đi tìm “thủ phạm” qua cột log_reuse_wait_desc trong sys.databases

Để biết chính xác lý do tại sao Log không thể tái sử dụng, bạn không cần phải đoán. SQL Server cung cấp một công cụ cực kỳ quyền lực là View hệ thống sys.databases.

Hãy chạy câu lệnh sau:

SQL

SELECT name, log_reuse_wait_desc 

FROM sys.databases 

WHERE name = ‘Ten_Database_Cua_Ban’;

Cột log_reuse_wait_desc sẽ trả về trạng thái hiện tại. Dưới đây là các “thủ phạm” phổ biến nhất:

Trạng thái: ACTIVE_TRANSACTION

Đây là nguyên nhân phổ biến nhất. Có một Transaction (giao dịch) nào đó đã bắt đầu nhưng chưa bao giờ Commit hoặc Rollback.

• Hệ quả: SQL Server phải giữ lại toàn bộ Log từ thời điểm Transaction đó bắt đầu để đảm bảo tính toàn vẹn dữ liệu.
• Cách xử lý: Sử dụng lệnh DBCC OPENTRAN để tìm ID của tiến trình (SPID) đang treo và yêu cầu người dùng kết thúc hoặc dùng lệnh KILL [SPID] nếu cần thiết.

Trạng thái: REPLICATION hoặc CDC (Change Data Capture)

Nếu bạn đang cấu hình Replication hoặc bật tính năng CDC, SQL Server sẽ không cắt cụt Log cho đến khi các thay đổi đó được đọc và gửi đi (đối với Replication) hoặc được Capture job xử lý (đối với CDC).

• Vấn đề: Nếu Agent dừng hoạt động hoặc kết nối giữa các Server bị ngắt, Log sẽ “xếp hàng” chờ đợi mãi mãi.
• Cách xử lý: Kiểm tra tình trạng hoạt động của SQL Server Agent và các Job liên quan.

Trạng thái: DATABASE_MIRRORING hoặc AVAILABILITY_GROUP

Trong các môi trường có tính sẵn sàng cao (High Availability), Log ở Node chính chỉ được cắt cụt khi nó đã được gửi và ghi thành công sang Node phụ (Secondary).

• Vấn đề: Nếu Node phụ bị sập hoặc đường truyền mạng bị nghẽn, Node chính sẽ giữ lại toàn bộ Log để chờ đồng bộ. Đây là kịch bản “thảm họa” có thể làm đầy ổ cứng của Node chính rất nhanh.
• Cách xử lý: Kiểm tra trạng thái đồng bộ (Synchronization State). Nếu Node phụ không thể phục hồi sớm, bạn có thể phải tạm thời gỡ bỏ Mirroring hoặc AG để cứu Node chính.

Trạng thái: LOG_BACKUP

Rất đơn giản, SQL Server đang báo cho bạn biết rằng: “Bạn đang để chế độ Full Recovery nhưng lại quên chưa cấu hình Backup Log”.

• Cách xử lý: Thực hiện ngay một bản Backup Log như đã hướng dẫn ở phần 1.

Kết luận

Chúng ta vừa hoàn thành quá trình theo dõi và quản lý log SQL Server của bkns, tại đây chúng ta có thể bắt đầu tối ưu quá trình giám sát hệ thống SQL Server hơn. Để sử dụng dịch vụ tại bkns các bạn có thể tham khảo các gói Cloud VPS và Server tạo và quản lý server của riêng mình.

Cảm ơn các bạn đã đọc bài viết này !

Bé Snake