解決 SQL Server 交易記錄檔無法縮減的實戰腳本

問題現象

資料庫本身僅 600 MB，交易記錄檔實際大小 33 MB，但佔用空間卻高達 2.8 GB。嘗試 SHRINK DATABASE、BACKUP LOG ... WITH TRUNCATE_ONLY 皆無效，疑似遇到舊版本已知問題。

解決方案：自動化縮減 T-SQL 腳本

將以下程式碼複製至查詢分析器，修改三個參數即可執行：

• @LogicalFileName：邏輯記錄檔名稱（可用 sp_helpfile 查詢）
• @NewSize：目標大小（MB）
• @MaxMinutes：允許跑多久（預設 10 分鐘）

SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
        @MaxMinutes INT,
        @NewSize INT

USE YourDatabaseName          -- 修改：目標資料庫
SELECT @LogicalFileName = 'YourDatabase_log'  -- 修改：邏輯記錄檔名
SELECT @MaxMinutes = 10,
       @NewSize = 100           -- 修改：目標大小 (MB)

DECLARE @OriginalSize int
SELECT @OriginalSize = size FROM sysfiles WHERE name = @LogicalFileName
PRINT 'Original log 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)

-- 迴圈寫入假資料強制記錄檔捲動
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 < 500
    BEGIN
        INSERT DummyTrans VALUES ('Fill log to force wrap')
        SELECT @Counter = @Counter + 1
    END
    EXEC (@TruncLog)
END

DROP TABLE DummyTrans
PRINT 'Shrink completed. Current log size: ' + 
      CAST((SELECT size FROM sysfiles WHERE name = @LogicalFileName) * 8 / 1024 AS varchar) + ' MB'

運作原理

1. 先嘗試直接 DBCC SHRINKFILE。
2. 若空間未釋放，透過大量 INSERT 產生交易，強制記錄檔循環覆寫（Wrap）。
3. 每次迴圈執行 BACKUP LOG ... WITH TRUNCATE_ONLY 截斷非作用段落。
4. 直到達到目標大小或超過時間上限。

注意事項

• 此腳本適用於 SQL Server 2000/2005；新版本建議改用 BACKUP LOG TO DISK 維護記錄鏈。
• 執行期間會產生大量 I/O，建議在維護時段進行。
• 完成後務必立即執行完整備份，重建記錄鏈。