萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

SQL Server 交易記錄檔收縮完整指南:截斷與收縮兩步驟

現象與誤區

頻繁寫入/刪除導致交易記錄檔 (LDF) 膨脹。許多人執行 `BACKUP LOG dbname WITH TRUNCATE_ONLY` (或 `NO_LOG`) 後發現檔案大小未變。**原因**:此指令僅標記非作用中虛擬記錄檔 (VLF) 可重複使用,**不會釋放檔案空間給作業系統**。必須再執行「收縮」動作實際釋放磁碟空間。

兩步驟標準流程 (SQL Server 2000/2005/2008/R2)

步驟 1:截斷記錄 (標記可重用)

-- 簡單復原模式下自動截斷;完整復原模式需先備份記錄或暫時切換
BACKUP LOG [YourDB] WITH TRUNCATE_ONLY  -- SQL 2000/2005/2008
-- 或
BACKUP LOG [YourDB] WITH NO_LOG         -- 同義,已棄用
-- SQL 2008 之後建議:
ALTER DATABASE [YourDB] SET RECOVERY SIMPLE;
-- 待收縮完成後再改回 FULL 並立即完整備份

步驟 2:收縮檔案 (DBCC SHRINKFILE / GUI)

GUI 操作 (企業管理員 / SSMS):

  1. 右鍵資料庫 → 所有工作 → 收縮資料庫 → **檔案** 標籤。
  2. 檔案類型選 **記錄 (Log)**。
  3. 勾選 **「釋放未使用的空間」 (Release unused space)** / `TRUNCATEONLY`。
  4. 確定執行。

T-SQL 指令:

-- 僅釋放未使用空間,不移動頁面 (最快、最安全)
DBCC SHRINKFILE (N'YourDB_Log', 0, TRUNCATEONLY)
-- 或指定目標大小 (MB),會嘗試移動頁面
DBCC SHRINKFILE (N'YourDB_Log', 100)  -- 收縮至 100 MB

替代方案:分離/刪除 LDF / 重新附加 (僅限緊急、小型資料庫)

  1. `sp_detach_db 'YourDB'`
  2. 手動刪除 `.ldf` 檔案。
  3. `sp_attach_db 'YourDB', 'path\YourDB.mdf'` → 系統提示找不到記錄檔,選「是」建立新 1MB 記錄檔。

缺點:資料庫離線、中斷所有連線、破壞記錄鏈 (完整復原模式下需立即完整備份)、大型資料庫耗時極長。**生產環境嚴禁常態使用。**

根本預防:正確的復原模式與備份策略

復原模式記錄行為適用場景備份需求
SIMPLECheckpoint 時自動截斷開發/測試、可容忍資料遺失至上次完整備份定期完整備份 (+ 差異)
FULL記錄保留至備份生產、需時間點還原完整備份 + 定期記錄備份 (如每 15 分鐘)
BULK_LOGGED大量作業最小記錄大量匯入/索引重建期間暫時切換同 FULL

常見問題排查

  • 記錄檔不收縮:檢查 `log_reuse_wait_desc` (SQL 2005+):`LOG_BACKUP` (需備份記錄)、`ACTIVE_TRANSACTION` (長執行交易)、`REPLICATION` (複寫延遲)、`DATABASE_MIRRORING` 等。
  • VLF 過多/過大:頻繁小幅成長導致 VLF 碎片化,影響啟動/還原/備份速度。建議預先設定合理初始大小與成長量 (固定 MB,非百分比)。
  • 收縮後又長大:調整成長量、檢查長交易、改用 SIMPLE 或加密記錄備份頻率。

最佳實務摘要

  1. 生產環境採用 **FULL 復原模式 + 定期記錄備份**。
  2. 記錄檔預先分配足夠空間,**固定 MB 成長** (如 512MB)。
  3. 需手動收縮時:`BACKUP LOG` → `DBCC SHRINKFILE (..., TRUNCATEONLY)`。
  4. 監控 `log_reuse_wait_desc` 與 VLF 數量,納入例行巡檢。
copyright © 萬盛學電腦網 all rights reserved