SQL Server 縮減資料庫與記錄檔大小完整指南：安全步驟、風險提示與最佳實踐

核心原則：先清記錄、再縮檔案、最後調整自動增長

盲目 DBCC SHRINKDATABASE 會導致**嚴重索引碎片**、檔案反覆增長碎片化磁碟、效能抖動。正確流程：

步驟 1：清空/截斷交易記錄檔 (前提：簡單復原模式或已備份記錄)

-- 方案 A: 簡單復原模式下自動截斷 (檢查點後)
ALTER DATABASE YourDB SET RECOVERY SIMPLE;
GO
-- 等待檢查點或手動觸發
CHECKPOINT;
GO

-- 方案 B: 完整復原模式 - 必須先備份記錄才能截斷
BACKUP LOG YourDB TO DISK = 'D:\Backup\YourDB_Log.trn' WITH INIT, COMPRESSION;
GO
-- 備份完成後，虛擬記錄檔 (VLF) 標記可重用，DBCC SHRINKFILE 才能真正釋放空間

禁用語法：DUMP TRANSACTION ... WITH NO_LOG / BACKUP LOG ... WITH NO_LOG (SQL 2008+ 已移除，**極度危險**，會破壞記錄鏈導致無法時間點還原)。

步驟 2：縮減記錄檔 (.ldf) 至合理大小

-- 1. 查詢邏輯檔名與當前大小
SELECT name, size/128.0 AS SizeMB, FILEPROPERTY(name,'SpaceUsed')/128.0 AS UsedMB
FROM sys.database_files WHERE type_desc = 'LOG';

-- 2. 縮減至目標大小 (例如 500MB)，需大於 UsedMB
DBCC SHRINKFILE (YourDB_Log, 500); -- 單位：MB
GO

-- 3. 驗證
SELECT name, size/128.0 AS SizeMB FROM sys.database_files WHERE type_desc = 'LOG';

關鍵**：SHRINKFILE 只能縮減到 **已使用空間** 以上。若 VLF 使用中 (長時間交易、複寫、鏡像、AlwaysOn 未同步)，無法縮減，需解決根因後再試。

步驟 3：縮減資料檔 (.mdf/.ndf) — 謹慎操作

-- 查詢資料檔使用情況
SELECT name, size/128.0 AS SizeMB, FILEPROPERTY(name,'SpaceUsed')/128.0 AS UsedMB
FROM sys.database_files WHERE type_desc = 'ROWS';

-- 縮減資料檔 (會產生大量碎片！)
DBCC SHRINKFILE (YourDB_Data, 2000); -- 目標 2000MB
GO

-- **立即重建索引消除碎片**
ALTER INDEX ALL ON YourTable REBUILD WITH (ONLINE = ON, FILLFACTOR = 90); -- 企業版
-- 或標準版離線重建
ALTER INDEX ALL ON YourTable REBUILD;
GO

警告：資料檔縮減會**打亂頁面順序**，導致索引碎片率飆升至 99%+，**必須緊接索引重建**。頻繁縮減/增長 = 效能殺手。

步驟 4：設定合理的自動增長 (避免未來再需縮減)

-- 修改檔案自動增長為固定大小 (非百分比)，啟用瞬間檔案初始化
ALTER DATABASE YourDB MODIFY FILE (NAME = YourDB_Data, FILEGROWTH = 512MB);
ALTER DATABASE YourDB MODIFY FILE (NAME = YourDB_Log, FILEGROWTH = 256MB);
GO

-- 檢查瞬間檔案初始化 (需 SQL 服務帳號有「執行量維護工作」權限)
-- 開啟後，資料檔擴容零填充，毫秒級完成；記錄檔仍需零填充。

高風險操作：分離資料庫刪除 .ldf 重建 (文中步驟 4) — **生產環境嚴禁**

**唯一適用場景**：開發/測試環境、.ldf 巨大且確認無關鍵交易、接受資料遺失、無備份鏈需求。

企業管理器 / SSMS 圖形化操作對應

1. 資料庫右鍵 → **工作** → **縮減** → **檔案**
2. 檔案類型選 **記錄** → 釋放未使用空間 / 縮減至指定 MB
3. 資料檔同理，但**勾選「遷移資料頁面」相當於 SHRINKFILE + 索引重組 (非重建)，碎片改善有限**

最佳實踐總結

1. 不要定期排程縮減！資料庫會自然增長至穩態大小。
2. **只有** 異常操作 (大量刪除、截斷分區、刪除大表) 導致大量空間釋出時，才考慮**一次性**縮減。
3. 縮減後**必重建索引** (維護窗口執行)。
4. 預先規劃檔案初始大小、自動增長固定 MB、啟用瞬間檔案初始化。
5. 記錄檔暴增根因排查：SELECT name, log_reuse_wait_desc FROM sys.databases; (常見：LOG_BACKUP、ACTIVE_TRANSACTION、REPLICATION、AVAILABILITY_REPLICA)。