SQL Server 交易記錄檔縮減實戰：SHRINK 失效時的強制縮減腳本

問題現象

資料庫 600MB，記錄檔 33MB 卻佔用 2.8GB 空間。SHRINK DATABASE、TRUNCATE LOG 均無效，疑為 SQL Server Bug。

強制縮減腳本（多次驗證有效）

複製至查詢分析器，修改三個參數（資料庫名、記錄檔邏輯名、目標大小 MB）執行即可：

SET NOCOUNT ON
DECLARE @LogicalFileName sysname, @MaxMinutes INT, @NewSize INT
USE YourDB -- 修改：資料庫名
SELECT @LogicalFileName = 'YourDB_log', -- 修改：記錄檔邏輯名
       @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

PRINT 'Final Size: ' + CAST(size * 8 / 1024 AS VARCHAR) + 'MB'
FROM sysfiles WHERE name = @LogicalFileName
DROP TABLE DummyTrans
SET NOCOUNT OFF

原理

透過不斷寫入/刪除虛擬資料填滿記錄檔，配合 BACKUP LOG ... WITH TRUNCATE_ONLY 截斷非作用中區段，再由 DBCC SHRINKFILE 釋放物理空間，在時間限制內循環直到達標。