SQL Server 壓縮記錄檔與資料檔完整操作指南：DUMP、BACKUP、SHRINK、分離附加、自動收縮與成長限制

操作順序鐵律：按步驟執行，未完成前一步勿執行下一步

步驟 1：清空記錄檔 (舊版語法，SQL 2000/2005 相容)

DUMP TRANSACTION 資料庫名 WITH NO_LOG;

或現代語法：

BACKUP LOG 資料庫名 WITH NO_LOG;  -- SQL 2008 後標記為過時，改用簡單模式截斷

步驟 2：截斷交易記錄 (配合簡單模式)

-- 1. 切換簡單復原模式
ALTER DATABASE 資料庫名 SET RECOVERY SIMPLE;
-- 2. CHECKPOINT 強制截斷
CHECKPOINT;
-- 3. (可選) 切回完整模式
ALTER DATABASE 資料庫名 SET RECOVERY FULL;

步驟 3：收縮檔案 (SSMS 圖形介面 / T-SQL)

SSMS 操作

1. 右鍵資料庫 → 工作 → 收縮 → 檔案
2. 檔案類型選 記錄 → 收縮方式：**收縮至指定大小** → 輸入建議最小值 → 確定
3. 檔案類型選 資料 → 同樣操作

T-SQL 指令

-- 收縮整個資料庫 (不建議常用)
DBCC SHRINKDATABASE (資料庫名);

-- 收縮指定檔案 (推薦)
-- 先查詢檔案編號
SELECT file_id, name, type_desc, physical_name FROM sys.database_files;
-- 再收縮 (1=資料檔, 2=記錄檔)
DBCC SHRINKFILE (2, 100);  -- 記錄檔壓縮至 100 MB

步驟 4：極致壓縮記錄檔 — 分離附加法 (高風險，非生產環境)

-- 1. 卸離
EXEC sp_detach_db @dbname = '資料庫名';
-- 2. 作業系統層面刪除 .ldf 檔案
-- 3. 附加單一資料檔 (自動建立最小 512KB 記錄檔)
EXEC sp_attach_single_file_db @dbname = '資料庫名',
    @physname = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\資料庫名.mdf';

警告：僅適用於**乾淨關閉**的資料庫。異常關閉會導致附加失敗或資料不一致。生產環境嚴禁。

步驟 5：設定自動收縮 (生產環境**強烈不建議**)

-- T-SQL
EXEC sp_dboption '資料庫名', 'autoshrink', 'TRUE';
-- 或
ALTER DATABASE 資料庫名 SET AUTO_SHRINK ON;

缺點：背景執行緒隨機運行 → 效能抖動、索引碎片化、鎖競爭。改用**維護計畫定期手動收縮 + 索引重建**。

步驟 6：限制記錄檔最大成長 (防止佔滿磁碟)

-- SSMS：資料庫屬性 → 檔案 → 記錄檔 → 自動增長 → 限制大小 (MB)
-- T-SQL
ALTER DATABASE 資料庫名 MODIFY FILE (NAME = 邏輯記錄檔名, MAXSIZE = 10240); -- 10 GB 上限

風險：達上限後寫入失敗，應用報錯。必須配合**監控告警 (剩餘 < 20% 觸發)** 及時擴容或備份截斷。

最佳實踐總結表