萬盛學電腦網

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

SQL Server 交易記錄檔過大縮減完整方案 (四大方法 + 生產環境最佳實務)

假設資料庫名為 test2。當記錄檔 (.ldf) 暴增時,提供四種處理方案,由緊急處理到生產環境最佳實務。

方法一:分離/附加單一檔案 (適用 7.0/2000,最快見效)

  1. Query Analyzer 執行:EXEC sp_detach_db 'test2'
  2. 檔案總管中將 test2_Log.LDF 改名 (如 test2_Log.old)。
  3. 執行:EXEC sp_attach_single_file_db 'test2', 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\test2.mdf'
  4. 成功則刪除改名後的舊記錄檔;失敗則改回檔名,用 sp_attach_db 重新附加雙檔案,改用方法二。

方法二:備份截斷 + DBCC SHRINK (適用 7.0/2000)

-- 7.0 / 2000
BACKUP LOG test2 WITH NO_LOG;
BACKUP LOG test2 WITH TRUNCATE_ONLY;
DBCC SHRINKDATABASE(test2);
-- 重複執行直到記錄檔縮小
NO_LOG / TRUNCATE_ONLY 僅標記非作用中虛擬記錄可覆寫,不減少物理大小;SHRINKDATABASE 才真正釋放磁碟空間。

方法三:設定截斷模式 + 自動收縮 (適用測試環境)

-- 6.x / 7.0
EXEC sp_dboption 'test2', 'trunc. log on chkpt.', 'true';
-- 7.0 / 2000
EXEC sp_dboption 'test2', 'autoshrink', 'true';
  • 檢查點時自動截斷記錄,並定期自動收縮檔案。
  • 僅適合測試/桌面環境,生產環境不建議啟用 autoshrink (可能在尖峰期觸發影響效能)。

方法四:完整復原模式 + 定期記錄備份 (生產環境標準做法)

-- 7.0 / 2000
EXEC sp_dboption 'test2', 'trunc. log on chkpt.', 'false';  -- 完整復原模式
EXEC sp_dboption 'test2', 'autoshrink', 'true';             -- 先開啟收縮縮小現有巨大記錄檔
-- 建立 SQL Agent 作業:
--   每 30 分鐘:BACKUP LOG test2 TO DISK = '...'
--   每天:     BACKUP DATABASE test2 TO DISK = '...'
-- 記錄檔收縮至正常大小後,將 autoshrink 設為 false。
生產系統啟用 autoshrink 風險較高 (可能在繁忙時段運行),但對無專職 DBA 的桌面/遠端系統是有效保護機制。

收縮機制原理

  • 物理大小減少觸發條件:DBCC SHRINKDATABASE、DBCC SHRINKFILE、自動收縮作業。
  • 先進行「記錄截斷」 (標記非作用中虛擬記錄),再由收縮作業刪除非作用中虛擬記錄釋放空間。

控制記錄檔大小策略

  • 維護記錄備份鏈:排程 BACKUP LOG 定期執行,防止記錄無限增長。
  • 不需記錄備份時:設定 簡單復原模式 (自動截斷)。

參考文獻

SQL Server 2000 線上叢書:
目錄 → SQL Server 架構 → 資料庫架構 → 實體資料庫架構 → 交易記錄架構 → 收縮交易記錄 / 截斷交易記錄

copyright © 萬盛學電腦網 all rights reserved