SQL Server 日誌壓縮與資料庫檔案瘦身完整指南（含風險提示）

⚠️ 先說風險：兩步慎用

• 步驟 4（分離資料庫刪除 LDF）：不安全，可能損壞資料庫或遺失資料
• 步驟 6（限制日誌最大值）：日誌達上限後後續操作將失敗，需清理後才能恢復

標準安全流程（推薦）

1. 清空交易日誌（簡易模式下）

DUMP TRANSACTION [資料庫名] WITH NO_LOG

2. 截斷交易日誌

BACKUP LOG [資料庫名] WITH NO_LOG

3. 收縮資料庫 / 檔案

GUI 操作： 企業管理器 → 右鍵資料庫 → 所有工作 → 收縮資料庫 → 收縮檔案 → 選擇日誌/資料檔案 → 「收縮至 XX MB」→ 輸入允許的最小值。

T-SQL：

-- 收縮整個資料庫
DBCC SHRINKDATABASE([資料庫名])

-- 收縮指定檔案（1 為檔案號，可用 SELECT * FROM sysfiles 查詢）
DBCC SHRINKFILE(1)

進階：極致壓縮日誌（需離線，僅限 SQL 7.0/2000 查詢分析器）

1. 分離資料庫：EXEC sp_detach_db @dbname = 'pubs'
2. 手動刪除 .ldf 檔案
3. 重新附加單一 MDF：EXEC sp_attach_single_file_db @dbname = 'pubs', @physname = '路徑\pubs.mdf'

⚠️ 此法會產生新的 500KB 左右日誌，**僅限緊急救援**，生產環境慎用。

自動化維護設定

啟用自動收縮（不建議常開，易產生碎片）

-- GUI：資料庫屬性 → 選項 → 勾選「自動收縮」
-- T-SQL：
EXEC sp_dboption '資料庫名', 'autoshrink', 'TRUE'

限制日誌成長上限

-- GUI：資料庫屬性 → 交易日誌 → 限制檔案成長
-- T-SQL：
ALTER DATABASE [資料庫名] MODIFY FILE (NAME = 邏輯檔名, MAXSIZE = 20MB)

作者常用一鍵腳本

DUMP TRANSACTION [jb51] WITH NO_LOG
BACKUP LOG [jb51] WITH NO_LOG
DBCC SHRINKDATABASE([jb51])

將 jb51 替換為實際資料庫名即可。

總結

• 優先用 BACKUP LOG + SHRINKFILE 標準流程
• 避免分離刪除 LDF、硬性限制 MAXSIZE
• 定期完整備份 + 交易日誌備份，才是控制日誌膨脹的根本之道