SQL Server 交易記錄檔縮小實戰：T-SQL 腳本與操作步驟完整指南

資料庫本體 600MB，記錄檔卻佔 2.8GB 是常見痛點。SHRINK DATABASE、TRUNCATE LOG 常無效。本文提供經實測可用的 T-SQL 腳本，三步驟完成記錄檔縮小。

核心腳本（複製到查詢分析器執行）

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

USE YourDatabase          -- 修改：目標資料庫名
SELECT @LogicalFileName = 'YourDatabase_log'  -- 修改：邏輯記錄檔名
SELECT @MaxMinutes = 10, @NewSize = 100       -- 修改：目標大小 (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)

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
SET NOCOUNT OFF

操作原理解析

1. DBCC SHRINKFILE 嘗試直接縮小
2. BACKUP LOG ... WITH TRUNCATE_ONLY 截斷非作用中虛擬記錄檔 (VLF)
3. 若未達目標，迴圈寫入/刪除虛擬資料「推進」記錄檔尾端，再截斷
4. 限制最長 10 分鐘避免無限迴圈

關鍵參數修改處

替代方案（簡單模式）

1. 屬性 → 選項 → 復原模式設為「簡單」
2. 所有工作 → 收縮資料庫 → 確定
3. 完成後改回「完整」模式（保留時間點還原能力）

注意： 生產環境建議維護完整復原模式，定期備份記錄檔才是根本解法。本腳本適用緊急縮小或開發/測試環境。

1. 上一篇文章： 升級 SQL Server 2005 的十大理由：分區、CLR、快照隔離、鏡像、Service Broker 全解析
2. 下一篇文章： SQL Server 查詢最佳化器三大 JOIN 演算法原理與選擇策略

關於SqlServer

C# 動態操作 SQL Server 完全指南：從建庫到架構變更的實戰技法

前言 撰寫資料庫應用程式時，透過 SQL 陳述式動態建立、修

SQL Server 2008 空間資料實戰 (一)：空間資訊基礎知識與座標系統

前言 SQL Server 2008 引入原生空間資料支援，

SQL Server 快速統計資料表筆數與空間佔用

一條查詢搞定所有使用者表的筆數與大小 在 Adventure

SQL Server 自製 Split 函數：將分隔字串拆解為資料表值

SQL Server 2016 前無內建 STRING_SP