SQL Server 2000 查詢分析器中收縮交易記錄檔實戰腳本

應用場景

• 記錄檔 (.ldf) 异常膨脹 (如 1GB+)，佔用磁碟空間
• 資料庫復原模式為 **完整** 但未定期備份記錄檔
• 需快速釋放空間，目標大小 (如 10MB)

核心原理

1. BACKUP LOG ... WITH TRUNCATE_ONLY (2000 專用) 截斷非作用中虛擬記錄檔 (VLF)
2. DBCC SHRINKFILE (邏輯檔名, 目標MB) 釋放檔案末端未使用空間
3. 若記錄檔未截斷 (作用中 VLF 在檔案末端)，需**製造虛擬交易** 將作用中 VLF 循環至開頭，再截斷收縮

完整可執行腳本 (SQL 2000 相容)

SET NOCOUNT ON
DECLARE @LogicalFileName SYSNAME, @MaxMinutes INT, @NewSize INT
USE Cisi                      -- 目標資料庫
SELECT @LogicalFileName = 'Cisi_log',  -- 邏輯檔名 (sp_helpfile 查詢)
       @MaxMinutes = 10,                 -- 最大嘗試分鐘數
       @NewSize = 10                     -- 目標大小 (MB)

DECLARE @OriginalSize INT
SELECT @OriginalSize = size FROM sysfiles WHERE name = @LogicalFileName
PRINT 'Original Size: ' + CAST(@OriginalSize * 8 / 1024 AS VARCHAR) + ' MB'

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)

-- 循環製造交易迫使 VLF 循環
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 Size: ' + CAST(size * 8 / 1024 AS VARCHAR) + ' MB'
FROM sysfiles WHERE name = @LogicalFileName

DROP TABLE DummyTrans

關鍵參數說明

⚠️ 重要警示與現代替代方案

最佳實踐檢查清單

• ☑ 確認邏輯檔名 (sp_helpfile / sys.database_files)
• ☑ 目標大小 ≥ 業務高峰期單位時間最大記錄量
• ☑ 收縮後**立即執行完整備份** (斷點還原鏈)
• ☑ 建立/驗證記錄檔備份排程作業
• ☑ 監控 log_reuse_wait_desc (2005+) 確認無異常等待

結語

收縮記錄檔是**治標**手段，**定期記錄檔備份**才是**治本**。SQL 2000 時代腳本雖經典，但現代版本應採用「簡單模式收縮 → 切回完整模式 → 立即備份」標準流程，並建立完備備份策略徹底解決記錄檔暴增問題。