SQL Server 資料庫與記錄檔 (LDF) 壓縮完整指南：三種方法與最佳實務

前言

資料庫長期運行會導致 .mdf/.ldf 膨脹，刪除資料不會自動釋放檔案空間。本文系統整理 Access/SQL Server 壓縮操作，重點闡述 SQL Server 三種記錄檔壓縮方法、簡單/完整復原模式切換策略、DBCC SHRINK 使用注意事項與生產環境最佳實務。

Access 壓縮 (簡單記錄)

工具 → 資料庫實用工具 → 「壓縮和修復資料庫」。

SQL Server 資料庫收縮

收縮資料庫 (DBCC SHRINKDATABASE) 主要釋放記錄檔空間，資料檔效果有限。建議定期執行防止 LDF 失控。

方法一：標準收縮流程 (UI / T-SQL)

1. 屬性 → 選項 → 復原模式 → 簡單。
2. 右鍵資料庫 → 工作 → 收縮 → 檔案 → 選擇記錄檔 → 釋放未使用空間 / 重組頁面後釋放。
3. 完成後將復原模式改回 完整 (生產環境必須)。

對應 T-SQL：

-- 切換至簡單模式
ALTER DATABASE [YourDB] SET RECOVERY SIMPLE;
GO

-- 收縮記錄檔 (檔案 ID 通常為 2)
DBCC SHRINKFILE (2, 100);  -- 目標縮減至 100 MB
GO

-- 切回完整模式
ALTER DATABASE [YourDB] SET RECOVERY FULL;
GO

-- 立即進行完整備份啟動新記錄鏈
BACKUP DATABASE [YourDB] TO DISK = '...';
GO

方法二：舊版常用技巧 (備份截斷 + 收縮)

-- 截斷記錄 (舊版相容)
BACKUP LOG [YourDB] WITH NO_LOG;  -- 或 TRUNCATE_ONLY
-- 收縮
DBCC SHRINKDATABASE ([YourDB]);  -- 或 SHRINKFILE

注意：NO_LOG/TRUNCATE_ONLY 在 SQL Server 2008 後已移除，請改用方法一。

方法三：離線分離刪除 LDF 重建 (極端情況)

1. SSMS → 右鍵資料庫 → 工作 → 分離 (勾選更新統計資料)。
2. 檔案系統中刪除/移動 .ldf。
3. SSMS → 右鍵資料庫 → 附加 → 選 .mdf → 系統提示缺少記錄檔 → 確定自動建立新 500KB LDF。
4. 重新設定資料庫使用者權限。

等效 T-SQL：

EXEC sp_detach_db 'YourDB', 'true';
-- 手工刪除 LDF
EXEC sp_attach_single_file_db 'YourDB', 'D:\Data\YourDB_data.mdf';

防止 LDF 再次暴增：永久設為簡單模式 (非生產/可接受資料遺失場景)

ALTER DATABASE [YourDB] SET RECOVERY SIMPLE;

簡單模式下 Checkpoint 會自動截斷非作用中記錄，LDF 維持穩定體積。

最佳實務總結

常見誤區

• 收縮資料庫 ≠ 收縮資料檔：SHRINKDATABASE 同時處理資料/記錄檔，易造成資料檔碎片；精準控制請用 SHRINKFILE 指定檔案 ID。
• 頻繁收縮有害：導致索引碎片、檔案碎片、自動成長效能抖動。設定合理 初始大小 與 自動成長增量 (建議固定 MB, 非百分比) 才是正道。
• 簡單模式不代表無記錄：仍記錄足以回滾交易的資訊，僅不保留歷史記錄供時間點還原。