SQL Server 2005 清理交易日誌檔案的完整指南

SQL Server 資料庫的交易日誌（LDF 檔案）會記錄所有對資料庫的修改操作。隨著時間增長，日誌檔案可能佔用大量磁碟空間，導致效能下降，甚至填滿系統磁碟。本文整理多種清理日誌的方法，適用於 SQL Server 2005 環境。

方法一：分離資料庫、刪除日誌檔、再附加資料庫

這是最徹底的方式，能強制重建一個極小的新日誌檔（約 500 KB）。操作步驟如下：

1. 完整備份資料庫：務必先建立完整備份，以防意外。
2. 分離資料庫：在 SSMS 中右鍵點選資料庫 → 「工作」 → 「分離」，勾選「刪除連接」。
3. 刪除 LDF 檔案：在檔案系統中手動刪除對應的 `.ldf` 檔案。
4. 附加資料庫：右鍵「資料庫」 → 「附加」，選擇 `.mdf` 檔案。系統會提示找不到日誌檔，點擊「確定」後會自動建立新的日誌檔。

也可使用 T-SQL 指令快速完成：

USE master;
EXEC sp_detach_db @dbname = 'TestDB';
EXEC sp_attach_single_file_db @dbname = 'TestDB', @physname = 'D:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\TestDB.mdf';

方法二：清空日誌（僅限 SQL Server 2000/2005）

使用 `DUMP TRANSACTION` 指令標記日誌為可覆寫，再配合收縮：

DUMP TRANSACTION TestDB WITH NO_LOG;
DBCC SHRINKFILE ('TestDB_log', 1);

注意：此指令在 SQL Server 2008 及更新版本已移除，不建議在生產環境頻繁使用，可能導致資料遺失風險。

方法三：截斷交易日誌（僅限 SQL Server 2000/2005）

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

同樣在 SQL Server 2008+ 不支援。

方法四：收縮資料庫/檔案

透過 SSMS 介面或 T-SQL 收縮日誌檔案：

-- 收縮指定日誌檔案至 1 MB
DBCC SHRINKFILE ('TestDB_log', 1);

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

方法五：設定自動收縮與檔案增長上限

• 自動收縮：資料庫屬性 → 選項 → 「自動收縮」設為 True，或執行 `EXEC sp_dboption '資料庫名', 'autoshrink', 'TRUE'`。
• 限制檔案增長：資料庫屬性 → 檔案 → 交易日誌 → 「限制檔案增長」設定最大值，或使用 `ALTER DATABASE` 指令。

額外技巧：不重啟服務清除系統錯誤日誌

當 SQL Server 錯誤日誌（ErrorLog）過大導致服務無法啟動時，可執行系統預存程序循環日誌檔案，無需重啟服務：

EXEC sp_cycle_errorlog;

此指令會將目前的 `ERRORLOG` 重新命名為 `ERRORLOG.1`，依此類推，並建立新的空白 `ERRORLOG`。

最佳實務建議

• 優先採用完整備份 + 交易記錄備份的維護計畫，定期截斷日誌，避免日誌暴增。
• 避免在生產環境直接刪除 LDF 檔案，除非資料庫已離線且有完整備份可還原。
• SQL Server 2008 及更新版本建議使用「簡單復原模式」搭配定期完整備份，或「完整復原模式」搭配定期記錄備份。