SQL Server 2005 交易記錄檔過大縮小實務：三種方法與自動化設定

方法一：備份截斷 + SHRINK（標準線上操作）

-- 1. 截斷記錄（簡單模式下自動截斷，完整模式需備份記錄或 NO_LOG）
BACKUP LOG [DBName] WITH NO_LOG;          -- 2005 支援，已棄用
BACKUP LOG [DBName] WITH TRUNCATE_ONLY;   -- 同義

-- 2. 縮小記錄檔
DBCC SHRINKDATABASE ([DBName]);           -- 整個資料庫
-- 或針對記錄檔（檔案編號通常為 2）
DBCC SHRINKFILE (2, TARGET_SIZE_MB);

方法二：離線分離/附加重建記錄檔（最徹底、停機最短）

1. SSMS → 資料庫右鍵 → 「工作」→「分離」→ 勾選「卸除連線」。
2. 檔案總管刪除 .ldf 記錄檔。
3. SSMS → 資料庫右鍵 → 「工作」→「附加」→ 選取 .mdf → 確定（自動建立極小新記錄檔）。

T-SQL 等效腳本：

EXEC sp_detach_db @dbname = 'DBName';
-- 手動刪除 .ldf 檔案
EXEC sp_attach_single_file_db @dbname = 'DBName', 
    @physname = 'C:\Path\DBName.mdf';

方法三：企業管理員圖形介面縮小（舊版工具）

企業管理員 → 資料庫右鍵 → 「所有工作」→「壓縮資料庫」→「壓縮檔案」→ 選擇記錄檔 →「壓縮至指定大小」→ 輸入最小可用值。

長效預防設定

• 限制記錄檔成長： 資料庫屬性 → 檔案 → 記錄檔 →「限制檔案成長」設定上限（如 2048 MB）。
• 自動壓縮（不建議）： ALTER DATABASE [DBName] SET AUTO_SHRINK ON; 或屬性 → 選項 →「自動壓縮」。易造成索引碎片與效能抖動。
• 簡單復原模式： ALTER DATABASE [DBName] SET RECOVERY SIMPLE; 適合非關鍵/可接受資料遺失場景，記錄自動截斷。
• 定期記錄備份（最佳）： 完整復原模式下，排程每 15-60 分鐘記錄備份，自然截斷非作用中 VLF，檔案大小穩定。

通用壓縮預存程序（進階）

文章提供 p_compdb 完整程式碼，整合備份、截斷、壓縮、分離/附加重建、自動壓縮設定，可部署於 master 定期執行。警告： 分離/附加步驟有損壞資料風險，生產環境請嚴格測試並備份後再使用。