萬盛學電腦網

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

SQL Server 事務日誌暴漲/寫滿完整解決方案

現象

  • 錯誤:The transaction log for database 'xxx' is full
  • 日誌檔 (LDF) 佔滿磁碟,資料庫無法寫入

簡單三步法(適用:可接受短暫中斷、簡單恢復模式)

  1. 切換簡單恢復模式:
    ALTER DATABASE [db] SET RECOVERY SIMPLE;
  2. 收縮日誌:
    DBCC SHRINKFILE (N'db_log', 100); -- 目標 100MB
  3. 切回完整/大量日誌模式:
    ALTER DATABASE [db] SET RECOVERY FULL;

標準完整流程(生產環境推薦)

  1. 立即備份日誌(保留還原鏈):
    BACKUP LOG [db] TO DISK = 'NUL:'; -- 緊急丟棄,僅為截斷
    -- 或正規備份:BACKUP LOG [db] TO DISK = 'D:\Backup\db_log.trn';
  2. 收縮日誌檔:
    DBCC SHRINKFILE (N'db_log', 1024);
  3. 設定自動增長固定 MB(避免百分比增長導致碎片):
    ALTER DATABASE [db] MODIFY FILE (NAME = N'db_log', FILEGROWTH = 512MB);
  4. 啟用自動收縮(謹慎):
    ALTER DATABASE [db] SET AUTO_SHRINK ON;
    ⚠️ 會碎片化索引,建議改用 Agent Job 定期 DBCC SHRINKFILE
  5. 設定最大檔案大小(防止再次寫滿磁碟):
    ALTER DATABASE [db] MODIFY FILE (NAME = N'db_log', MAXSIZE = 50GB);

極端情況:分離/刪除 LDF 重建(高風險,最後手段)

-- 1. 離線資料庫
ALTER DATABASE [db] SET OFFLINE WITH ROLLBACK IMMEDIATE;
-- 2. 分離
EXEC sp_detach_db 'db';
-- 3. 重命名/刪除 .ldf 檔案
-- 4. 僅附加 .mdf(自動建新 LDF,~500KB)
EXEC sp_attach_single_file_db @dbname = 'db', 
    @physname = 'D:\Data\db.mdf';

⚠️ **風險**:未提交事務丟失、復制/鏡像/AlwaysOn 拓撲破壞、時間點還原鏈中斷。

根因預防:為什麼日誌會暴漲?

原因對策
完整恢復模式下無日誌備份建立定期 BACKUP LOG 作業(每 15-60 分鐘)
長時間未提交的大事務拆分批次、避免長事務、監控 sys.dm_tran_active_transactions
大量批次操作(BULK INSERT/INDEX REBUILD)改用 BULK_LOGGED 模式或分批提交
複制/鏡像/可用性群組延遲監控同步延遲,修復傳輸瓶頸

監控告警腳本

-- 日誌使用率 > 80% 告警
SELECT 
    DB_NAME(database_id) AS db_name,
    (log_reuse_wait_desc) AS wait_reason,
    CAST(log_space_used_size_mb AS DECIMAL(10,2)) / log_space_total_mb * 100 AS pct_used
FROM sys.dm_db_log_space_usage
WHERE CAST(log_space_used_size_mb AS DECIMAL(10,2)) / log_space_total_mb > 0.8;

**核心觀念:日誌暴漲是症狀,缺乏日誌備份或長事務才是病根。治標(收縮)易,治本(備份策略+事務設計)才關鍵。**

copyright © 萬盛學電腦網 all rights reserved