SQL Server 2005 清除交易記錄檔 (LDF) 多種方法與最佳實務

SQL Server 2005 交易記錄檔 (.ldf) 長期增長會佔滿磁碟、拖慢效能。本文整理四種清理方法、收縮指令、自動收縮設定與錯誤日誌循環技巧，並說明各方法適用版本與風險。

方法一：分離 → 刪除 LDF → 附加（最徹底，需停機）

1. 完整備份資料庫（必要）。
2. SSMS：資料庫右鍵 → 工作 → 分離 → 勾選 卸除連接 → 確定。
3. 檔案總管刪除 xxx_log.ldf。
4. SSMS：資料庫右鍵 → 工作 → 附加 → 選擇 .mdf → 系統提示找不到記錄檔 → 移除記錄檔項目 → 確定。
5. 系統自動建立新 .ldf (約 504KB)。

T-SQL 等效：

USE master;
EXEC sp_detach_db @dbname = 'TestDB';
EXEC sp_attach_single_file_db @dbname = 'TestDB', 
    @physname = 'D:\Data\TestDB.mdf';

方法二：DUMP TRANSACTION ... WITH NO_LOG (SQL 2005/2000 支援，2008 移除)

DUMP TRANSACTION TestDB WITH NO_LOG;
DBCC SHRINKFILE ('TestDB_log', 1);  -- 收縮至 1MB

方法三：BACKUP LOG ... WITH NO_LOG (同樣 2008 移除)

BACKUP LOG TestDB WITH NO_LOG;
DBCC SHRINKFILE ('TestDB_log', 1);

方法四：DBCC SHRINKFILE / SHRINKDATABASE（標準收縮）

-- 僅收縮記錄檔
DBCC SHRINKFILE (2, 1);  -- file_id=2 通常為 log，目標 1MB

-- 或收縮整個資料庫
DBCC SHRINKDATABASE (TestDB);

SSMS 圖形介面：資料庫右鍵 → 工作 → 收縮 → 檔案 → 選擇 記錄 → 收縮至 輸入最小可用 MB。

自動收縮設定（不建議長期啟用）

-- T-SQL
EXEC sp_dboption 'TestDB', 'autoshrink', 'TRUE';

-- SSMS：資料庫屬性 → 選項 → 自動收縮 = True

缺點：頻繁收縮/增長造成碎片、效能抖動。建議排程手動收縮（如每月一次）。

限制記錄檔最大成長

ALTER DATABASE TestDB MODIFY FILE (NAME = TestDB_log, MAXSIZE = 2048MB);

或 SSMS：資料庫屬性 → 檔案 → 記錄檔 → 限制檔案增長。

不重啟服務循環錯誤日誌

EXEC sp_cycle_errorlog;

效果：將目前 ERRORLOG 更名為 ERRORLOG.1，以此類推，產生新空 ERRORLOG。避免日誌過大導致服務啟動失敗。

版本相容性總結

最佳實務建議

1. 復原模式設定：非時間點還原需求資料庫設為 SIMPLE，記錄自動截斷，無需手動清理。
2. FULL 模式：定期 BACKUP LOG 至磁碟/帶庫，配合 DBCC SHRINKFILE 維持合理大小。
3. 禁止直接刪除 LDF：除非分離資料庫，否則會導致資料庫損壞。
4. 監控記錄檔使用率：DBCC SQLPERF(LOGSPACE) 定期巡檢。

選擇適合復原模式與業務 SLA 的記錄管理策略，比事後被動清理更有效率。