萬盛學電腦網

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

SQL Server 交易記錄檔暴增清理與壓縮完整指南(2005/2008/2012+)

為什麼記錄檔會暴增?

完整復原模式下,每筆異動皆記入交易記錄檔(LDF),若未定期進行記錄檔備份,LDF 會無限增長,最終佔滿磁碟導致服務停擺。

版本別清理方案

SQL Server 2005

-- 1. 截斷記錄檔(舊語法,已棄用但仍可用)
BACKUP LOG [DBName] WITH NO_LOG;
-- 或
DUMP TRANSACTION [DBName] WITH NO_LOG;

-- 2. 收縮檔案(檔案 ID 通常為 2)
USE [DBName];
DBCC SHRINKFILE (2, 1);  -- 目標壓縮至 1MB
GO

警告:NO_LOG 會破壞記錄鏈,後續必須立即進行完整備份重新建立鏈。

SQL Server 2008 / 2008 R2 / 2012+(推薦標準流程)

USE [master];
GO

-- 1. 切換至簡單復原模式(自動截斷非作用中 VLF)
ALTER DATABASE [DBName] SET RECOVERY SIMPLE WITH NO_WAIT;
GO

-- 2. 壓縮記錄檔(指定邏輯檔名,通常為 DBName_log)
USE [DBName];
DBCC SHRINKFILE (N'DBName_log', 11, TRUNCATEONLY);  -- 保留 11MB
GO

-- 3. 切回完整復原模式
USE [master];
ALTER DATABASE [DBName] SET RECOVERY FULL WITH NO_WAIT;
GO

-- 4. 立即執行完整備份,重新啟動記錄鏈
BACKUP DATABASE [DBName] TO DISK = N'路徑\DBName_Full.bak' WITH INIT, COMPRESSION, STATS=10;
GO

-- 5. 立即執行記錄檔備份
BACKUP LOG [DBName] TO DISK = N'路徑\DBName_Log.trn' WITH INIT, COMPRESSION, STATS=10;
GO

SQL Server 2000(舊版參考)

DUMP TRANSACTION [jb51] WITH NO_LOG;
BACKUP LOG [jb51] WITH NO_LOG;
DBCC SHRINKDATABASE();

關鍵細節

  • 邏輯檔名確認:SELECT file_id, name FROM sys.database_files; 找出 type=1 (LOG) 的 name。
  • TRUNCATEONLY 僅釋放檔案末端未使用空間,不移動資料頁,速度快且不產生碎片。
  • 目標大小(如範例 11MB)應略大於正常作用中 VLF 總量,避免頻繁自動增長。
  • 嚴禁 直接刪除 .ldf 檔案或使用「卸離/刪除檔案/重新附加」大法,極高風險導致資料庫損毀。

長效機制:防止再次暴增

  1. 建立 定期記錄檔備份作業(如每 15-60 分鐘一次)。
  2. 設定 自動增長固定大小(MB)而非百分比,避免大檔案增長卡頓。
  3. 啟用 資料庫郵件告警:LDF 使用率 > 80% 或磁碟剩餘 < 20% 時通知。
  4. 大量匯入/重建索引等操作前,暫時切至 BULK_LOGGED 模式,事後切回並備份。

緊急情況:記錄檔已滿磁碟,無法備份

若磁碟已無空間無法寫入備份檔:

  1. 在另一顆有空間的磁碟建立新記錄檔:ALTER DATABASE [DBName] ADD LOG FILE (NAME='DBName_log2', FILENAME='D:\NewDisk\DBName_log2.ldf', SIZE=100MB);
  2. 執行記錄檔備份至新磁碟。
  3. 再按標準流程壓縮原記錄檔,最後移除新增記錄檔。
copyright © 萬盛學電腦網 all rights reserved