SQL Server 資料庫 Log 檔成長行為與定期備份實測分析

 

資料庫 Log 檔成長行為與定期備份實測分析

記錄交易時產生大量 Log，Log 檔案隨交易成長。
執行 Log 備份僅釋放可重用空間，實體檔案大小不變。
執行 Log 壓縮可縮小實體檔案。
以下以 YourDataBase 測試未備份與定期備份 Log 的差異。



案例 A：未備份 Log

執行兩次大量更新後，Log 檔案持續成長。

use YourDataBase;
GO
SELECT N'初始' AS action_desc,
       total_log_size_in_bytes/1024/1024 AS TotalLogSize_MB,
       used_log_space_in_bytes/1024/1024 AS UsedLogSpace_MB,
       format(used_log_space_in_percent,'#0.00') AS UsedLogSpace_Percent
FROM sys.dm_db_log_space_usage
WHERE database_id = DB_ID('YourDataBase');
GO

update YourTable set YourColumn = YourColumn where ID <= 1000000;
GO

SELECT N'大量更新資料' AS action_desc,
       total_log_size_in_bytes/1024/1024,
       used_log_space_in_bytes/1024/1024,
       format(used_log_space_in_percent,'#0.00')
FROM sys.dm_db_log_space_usage
WHERE database_id = DB_ID('YourDataBase');
GO

update YourTable set YourColumn = YourColumn where ID <= 1000000;
GO

SELECT N'大量更新資料 2' AS action_desc,
       total_log_size_in_bytes/1024/1024,
       used_log_space_in_bytes/1024/1024,
       format(used_log_space_in_percent,'#0.00')
FROM sys.dm_db_log_space_usage
WHERE database_id = DB_ID('YourDataBase');
GO

結果：

action_descTotalLogSize (MB)UsedLogSpace (MB)UsedLogSpace (%)
初始1034.52
大量更新資料70125235.95
大量更新資料 290150155.68


案例 B：定期備份 Log

每次更新後立即備份 Log，可維持 Log 檔案大小不持續成長。

use YourDataBase;
GO
SELECT N'初始' AS action_desc,
       total_log_size_in_bytes/1024/1024,
       used_log_space_in_bytes/1024/1024,
       format(used_log_space_in_percent,'#0.00')
FROM sys.dm_db_log_space_usage
WHERE database_id = DB_ID('YourDataBase');
GO

update YourTable set YourColumn = YourColumn where ID <= 1000000;
GO

SELECT N'大量更新資料' AS action_desc,
       total_log_size_in_bytes/1024/1024,
       used_log_space_in_bytes/1024/1024,
       format(used_log_space_in_percent,'#0.00')
FROM sys.dm_db_log_space_usage
WHERE database_id = DB_ID('YourDataBase');
GO

DECLARE @DateTime nvarchar(20)=REPLACE(CONVERT(varchar(8),GETDATE(),112)+'_'+REPLACE(CONVERT(varchar(8),GETDATE(),108),':',''),' ','');
DECLARE @FileName nvarchar(260)=N'F:\SQL_DATA_Backup\YourDataBase_'+@DateTime+N'.trn';
EXEC('BACKUP LOG YourDataBase TO DISK = N'''+@FileName+N''' WITH INIT, COMPRESSION;');
GO

SELECT N'備份 Log 檔案' AS action_desc,
       total_log_size_in_bytes/1024/1024,
       used_log_space_in_bytes/1024/1024,
       format(used_log_space_in_percent,'#0.00')
FROM sys.dm_db_log_space_usage
WHERE database_id = DB_ID('YourDataBase');
GO

update YourTable set YourColumn = YourColumn where ID <= 1000000;
GO

SELECT N'大量更新資料 2' AS action_desc,
       total_log_size_in_bytes/1024/1024,
       used_log_space_in_bytes/1024/1024,
       format(used_log_space_in_percent,'#0.00')
FROM sys.dm_db_log_space_usage
WHERE database_id = DB_ID('YourDataBase');
GO

DECLARE @DateTime nvarchar(20)=REPLACE(CONVERT(varchar(8),GETDATE(),112)+'_'+REPLACE(CONVERT(varchar(8),GETDATE(),108),':',''),' ','');
DECLARE @FileName nvarchar(260)=N'F:\SQL_DATA_Backup\YourDataBase_'+@DateTime+N'.trn';
EXEC('BACKUP LOG YourDataBase TO DISK = N'''+@FileName+N''' WITH INIT, COMPRESSION;');
GO

SELECT N'備份 Log 檔案' AS action_desc,
       total_log_size_in_bytes/1024/1024,
       used_log_space_in_bytes/1024/1024,
       format(used_log_space_in_percent,'#0.00')
FROM sys.dm_db_log_space_usage
WHERE database_id = DB_ID('YourDataBase');
GO

結果：

action_descTotalLogSize (MB)UsedLogSpace (MB)UsedLogSpace (%)
初始1034.52
大量更新資料70125235.95
備份 Log70125235.96
大量更新資料 270126337.52
備份 Log70126337.25

DBA 建議

  • 建立固定間隔 Log 備份排程

  • 使用完整模式時必須定期備份 Log 防止無限制成長

  • 持續監控 sys.dm_db_log_space_usage

  • 大量批次更新前確認 Log 備份正常

  • 設定 Autogrowth 為固定大小避免碎片化

  • 僅在必要時執行 Log 壓縮避免 I/O 壓力