Backup Log trong SQL Server

Bên cạnh hai loại FullDifferential backup, SQL Server còn có loại thứ ba là Log backup. Nếu database của bạn ở chế độ simple recovery bạn không cần quan tâm đến loại Log backup này. Nhưng nếu sử dụng chế độ full hoặc bulk-logged recovery bạn buộc phải thực hiện việc backup log thường xuyên.

Cú pháp của câu lệnh backup log như sau


BACKUP LOG [tên_database] TO DISK = 'C:\ten_database.trn'
GO

Backup log sẽ giúp ngăn chặn việc transaction log của bạn sẽ bị đầy lên bằng cách truncate phần log cũ, không có transaction đang hoạt động nào trên đó. Ngoài ra, backup log files còn hỗ trợ việc khôi phục database tới một thời điểm cụ thể, điều mà full và different backup không thể làm được.

Việc truncate phần log cũ ám chỉ hành động xóa những log record trong VLF (virtual log file) đã được đánh dấu là inactive. Những VLF này sau đó sẽ được tái sử dụng cho việc chứa những dòng log mới chứ không phải trả lại cho hệ điều hành. Do đó, sau khi thực hiện backup log kích thước transaction log file của database sẽ không giảm một tí nào.

Một VLF chỉ được đánh dấu là inactive khi không chứa log của một active transaction hoặc không chứa log đang đợi để được các chức năng mang qua server khác như Replication, Mirroring, Always On Availability Group.

Nếu bạn muốn giảm kích thước transaction log, bạn phải thực hiện việc shrink files sau khi truncate log.

Chúng ta cùng đi qua phần demo sau đây để hiểu rõ hơn công dụng của việc backup log cho một database có cơ chế full recovery. Để dễ theo dõi mình sẽ tóm tắt các ý đồ trong phần demo gồm các bước

  • Tạo một database với recovery model là full
  • Tạo bảng và đổ dữ liệu vào để thấy SQL Server chỉ tăng kích thước log theo qua trình insert
  • Thực hiện backup log, kiểm tra để thấy kích thước không đổi sau khi backup log

Tạo database với câu lệnh sau

USE MASTER
GO
CREATE DATABASE TestLogBackup  
ON PRIMARY
    ( NAME = N'TestLogBackup', FILENAME = N'C:\user_db\TestLogBackup.mdf' , SIZE = 8192KB)
LOG ON
    ( NAME = N'qtcsdl_log', FILENAME = N'C:\user_db\TestLogBackup.ldf' , SIZE = 8192KB , FILEGROWTH = 8192KB )
GO 

USE [master]
GO
ALTER DATABASE TestLogBackup SET RECOVERY FULL WITH NO_WAIT
GO

Tiếp đến, chúng ta sẽ tạo bảng có cấu trúc như script bên dưới

USE TestLogBackup
GO

CREATE TABLE Test(insertedDate DATETIME, [data] CHAR(2000))
GO

Chúng ta insert dữ liệu vào bảng này để phát sinh log, và sử dụng câu lệnh DBCC SQLPERF(LOGSPACE) để theo dõi kích thước transaction log file cũng như phần trăm đã sử dụng.


USE TestLogBackup
GO
INSERT INTO Test
SELECT GETDATE(), ''
GO 4000
 
GO
DECLARE @dblogspace TABLE(
	[Database Name] SYSNAME,
	[Log Size (MB)] FLOAT, 
	[Log Space Used (%)] FLOAT, 
	[Status] INT
	)
INSERT INTO @dblogspace
EXEC ('DBCC sqlperf(logspace)')

SELECT * 
FROM @dblogspace
WHERE [Database Name] = 'TestLogBackup'

Để quan sát rõ hơn việc transaction log file cứ tăng hoài theo thời gian, bạn hãy chạy script trên vài lần và theo dõi kết quả sau mỗi lần chạy để thấy sự tăng trưởng. Kết quả trên máy của mình sau bảy lần chạy như hình dưới.

Thông tin sử dụng transaction log file.

Nếu bạn cứ insert hoài mà không thực hiện backup log cho database TestLogBackup này thì sớm hay muộn cũng sẽ dẫn tới tình trạng đầy ổ đĩa chứa log file. Chúng ta thử chạy câu lệnh backup log rồi quan sát kích thước xem thế nào.


GO
BACKUP DATABASE TestLogBackup TO DISK = 'C:\user_db\BACKUP\TestLogBackup.bak'

GO

BACKUP LOG TestLogBackup TO DISK = N'C:\user_db\BACKUP\TestLogBackup_20220411_1601'

GO
DECLARE @dblogspace TABLE(
	[Database Name] SYSNAME,
	[Log Size (MB)] FLOAT, 
	[Log Space Used (%)] FLOAT, 
	[Status] INT
	)
INSERT INTO @dblogspace
EXEC ('DBCC sqlperf(logspace)')

SELECT * 
FROM @dblogspace
WHERE [Database Name] = 'TestLogBackup'

Bạn cần có full backup đầu tiên sau khi tạo database hoặc sau khi chuyển từ các recovery model khác sang full recovery mới có thể thực hiện backup log. Đó là lý do mình chèn câu lệnh full backup trước backup log trong script trên, một điều cần lưu ý nữa là full backup không ảnh hưởng gì đến transaction log backup.

Kết quả sau khi chạy backup log trên máy của mình như hình dưới đây.

Sau khi thực hiện backup log.

Giá trị cột [Log Space Used (%)] đã giảm từ 95.9% xuống còn 2.5%. Nghĩa là transaction log file của bạn có nhiều không gian trống hơn để chứa log.

Bây giờ chúng ta kết hợp việc backup log sau mỗi lần có một lượng lớn insert, để xem transaction log file thay đổi thế nào trong một quá trình như vậy. Script của chúng ta sẽ làm các việc sau

  • Insert 4000 dòng vào bảng Test
  • kiểm tra kích thước log và phần trăm đã dùng
  • chạy backup log
  • kiểm tra kích thước log và phần trăm đã dùng

USE TestLogBackup
GO
INSERT INTO Test
SELECT GETDATE(), ''
GO 4000
 
GO
DECLARE @dblogspace TABLE(
	[Database Name] SYSNAME,
	[Log Size (MB)] FLOAT, 
	[Log Space Used (%)] FLOAT, 
	[Status] INT
	)
INSERT INTO @dblogspace
EXEC ('DBCC sqlperf(logspace)')

SELECT * 
FROM @dblogspace
WHERE [Database Name] = 'TestLogBackup'

DECLARE @backuppath NVARCHAR(1024)
SET @backuppath = N'C:\user_db\BACKUP\TestLogBackup_' +
				+ REPLACE(CONVERT(VARCHAR, GETDATE(),112),'/','') + REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')
				+ '.trn'
				
BACKUP LOG TestLogBackup TO DISK = @backuppath

DELETE FROM @dblogspace

INSERT INTO @dblogspace
EXEC ('DBCC sqlperf(logspace)')

SELECT * 
FROM @dblogspace
WHERE [Database Name] = 'TestLogBackup'

Bạn có thể chạy vài lần để thấy sự khác biết, bây giờ log file không liên tục tăng trưởng như lúc đầu nữa. Với việc backup log thường xuyên như trong script trên chúng ta thấy SQL Server đã thu hồi và tái sử dụng phần không gian chứa log cũ sau khi truncate bằng câu lệnh backup log.

Sau mỗi lần chạy log file luôn có đủ không gian trống cho những dòng log mới.

Vậy là chúng ta đã thấy sự thiết yếu của việc thực hiện backup log khi sử dụng database ở chế độ full recovery, điều này sẽ giúp bạn tránh khỏi hàng loạt rắc rối khi log file chỉ tăng không giảm. Vấn đề còn lại là việc lên lịch thực hiện backup log cho phù hợp với cường độ thay đổi data của database đó.

Leave a Reply