SQL Server 2005 清理交易記錄檔的多種實用方法

使用 SQL Server 資料庫的開發人員都知道一個常見問題：系統盤（通常是 C 槽）空間很容易被佔滿。由於資料庫在運作過程中會導致交易記錄檔不斷增長，不僅佔用大量磁碟空間，還會拖慢資料庫效能。SQL Server 的每個資料庫都有對應的 .ldf 記錄檔，記錄使用者對資料庫的修改操作。本文整理了幾種清除資料庫記錄檔的實用方法，包含直接刪除記錄檔、清空記錄、壓縮資料庫檔案以及截斷交易記錄等。

方法一：分離資料庫並刪除 LDF 檔案

這是最徹底的清理方式，但操作前務必先做完整備份。

1. 在 SSMS 中右鍵點選目標資料庫 → 「工作」 → 「分離」。
2. 在分離視窗中勾選「刪除連接」，確認後執行分離。分離完成後，資料庫將不再出現在資料庫清單中。
3. 透過檔案總管手動刪除對應的 .ldf 記錄檔。
4. 右鍵點選「資料庫」 → 「附加」，選取對應的 .mdf 主資料檔。系統會提示找不到記錄檔，點選「移除」該記錄檔項目後確定附加。SQL Server 會自動建立一個新的、極小（約 504 KB）的記錄檔。

也可使用 T-SQL 指令快速完成上述流程：

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

方法二：清空交易記錄（僅限 SQL Server 2000/2005）

注意：SQL Server 2008 及更新版本已不支援此指令。

DUMP TRANSACTION TestDB WITH NO_LOG;

方法三：壓縮資料庫檔案

使用 DBCC SHRINKFILE 將指定的記錄檔壓縮至目標大小（單位為 MB）。

DBCC SHRINKFILE ('TestDB_log', 1);

方法四：截斷交易記錄（僅限 SQL Server 2000/2005）

同樣不支援 SQL Server 2008 及以上版本。

BACKUP LOG TestDB WITH NO_LOG;

綜合維護腳本參考

以下腳本整理了常見的記錄檔維護步驟，包含備份、清空、壓縮、分離/附加重建、設定自動收縮與限制檔案成長等。建議在維護視窗執行，並提前做好完整備份。

-- 1. 清空記錄
DUMP TRANSACTION [資料庫名] WITH NO_LOG;

-- 2. 截斷交易記錄
BACKUP LOG [資料庫名] WITH NO_LOG;

-- 3. 壓縮資料庫 / 指定檔案
DBCC SHRINKDATABASE([資料庫名]);
-- 或指定檔案編號 (可透過 SELECT * FROM sysfiles 查詢)
DBCC SHRINKFILE(1);

-- 4. 分離/附加重建記錄檔 (風險較高，謹慎使用)
-- EXEC sp_detach_db @dbname = '資料庫名';
-- 手動刪除 .ldf 檔案後
-- EXEC sp_attach_single_file_db @dbname = '資料庫名', @physname = 'mdf完整路徑';

-- 5. 設定自動收縮
EXEC sp_dboption '資料庫名', 'autoshrink', 'TRUE';

-- 6. 限制記錄檔最大成長
ALTER DATABASE [資料庫名] MODIFY FILE (NAME = 邏輯檔名, MAXSIZE = 20MB);

快速收縮至 1 MB 的簡易腳本

DUMP TRANSACTION [資料庫名] WITH NO_LOG;
DBCC SHRINKDATABASE('[資料庫名]', TRUNCATEONLY);

不重啟服務清除 SQL Server 系統錯誤記錄

當 SQL Server 的系統錯誤記錄檔過大導致服務無法啟動時，無需停止服務即可循環記錄檔：

EXEC sp_cycle_errorlog;

原理說明： 每次啟動 SQL Server 時，目前的錯誤記錄會重新命名為 errorlog.1，舊的依序遞增。sp_cycle_errorlog 可在不重啟服務的情況下手動觸發此循環機制，釋放空間。