SQL Server 交易記錄檔 (LDF) 無限增長是資料庫管理員常見的頭痛問題。本文整理四種清理壓縮方法,從標準操作到緊急處理,依風險等級由低到高說明。
此為微軟官方建議的標準流程,透過備份記錄檔使其可重複使用,再壓縮物理檔案。
BACKUP LOG [資料庫名稱] WITH NO_LOG
注意:SQL Server 2008 以上版本已移除 WITH NO_LOG,改用 BACKUP LOG [資料庫名稱] TO DISK='NUL:'(測試環境)或正規備份至磁碟。
將資料庫復原模式從「完整」改為「簡單」,記錄檔在檢查點自動截斷,不再無限增長。
透過指令碼動態計算並收縮記錄檔至指定大小,適合納入定期維護作業。
SET NOCOUNT ON
DECLARE @LogicalFileName sysname, @MaxMinutes INT, @NewSize INT
USE [YourDatabase] -- 目標資料庫名稱
SELECT @LogicalFileName = 'YourDatabase_log', -- 邏輯記錄檔名,可用 sp_helpfile 查詢
@MaxMinutes = 10,
@NewSize = 100 -- 目標大小 (MB)
DECLARE @OriginalSize INT
SELECT @OriginalSize = size FROM sysfiles WHERE name = @LogicalFileName
-- 建立虛擬交易填滿記錄檔以觸發截斷
CREATE TABLE DummyTrans (DummyColumn CHAR(8000) NOT NULL)
DECLARE @Counter INT, @StartTime DATETIME, @TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(), @TruncLog = 'BACKUP LOG ' + DB_NAME() + ' WITH TRUNCATE_ONLY'
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
WHILE @MaxMinutes > DATEDIFF(mi, @StartTime, GETDATE())
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)
AND (@OriginalSize * 8 / 1024) > @NewSize
BEGIN
SELECT @Counter = 0
WHILE (@Counter < @OriginalSize / 16) AND (@Counter < 50000)
BEGIN
INSERT DummyTrans VALUES ('Fill Log')
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END
EXEC (@TruncLog)
END
SELECT 'Final Log Size: ' + CONVERT(VARCHAR(30), size*8/1024) + ' MB'
FROM sysfiles WHERE name = @LogicalFileName
DROP TABLE DummyTrans
SET NOCOUNT OFF
⚠ 警告:可能造成資料遺失,僅限非生產環境或已完整備份時使用
.LDF 實體檔案.MDF 檔案Auto Shrink 需謹慎,建議改由維護計畫定期收縮DBCC SQLPERF(LOGSPACE) 追蹤記錄檔使用率sp_helpfile)掌握這四種方法,並建立定期記錄檔備份機制,即可徹底解決 SQL Server 記錄檔暴增問題,保障資料庫穩定運行。