您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 交易記錄寫滿：根因分析與緊急處置全流程

編輯：關於SqlServer

交易記錄 (Transaction Log) 寫滿：從根因診斷到空間回收完整指南

錯誤 9002 / 1105：The transaction log for database 'xxx' is full。資料庫停止接受寫入，業務癱瘓。本文系統梳理 7 大根因、即時緩解步驟、長效容量規劃與監控體系。

七大核心根因

#	根因	典型徵兆	驗證查詢
1	長時間未執行記錄備份 (完整/大量記錄模式)	log_reuse_wait_desc = LOG_BACKUP	SELECT name, log_reuse_wait_desc FROM sys.databases
2	長跑交易未提交/回滾	log_reuse_wait_desc = ACTIVE_TRANSACTION	DBCC OPENTRAN; sys.dm_tran_active_transactions
3	大量批次操作 (BULK INSERT/大量 UPDATE/DELETE)	單次交易產生大量記錄	查詢計劃 + sys.dm_exec_requests
4	檢查點無法推進 (簡單模式)	log_reuse_wait_desc = CHECKPOINT	錯誤日誌檢查 checkpoint 失敗
5	異步鏡像/可用性群組同步延遲	log_reuse_wait_desc = DATABASE_MIRRORING / AVAILABILITY_REPLICA	sys.database_mirroring / sys.availability_replicas
6	複寫發佈端記錄讀取器延遲	log_reuse_wait_desc = REPLICATION	Replication Monitor / sys.dm_repl_tranhash
7	自動成長受限或磁碟空間耗盡	max_size 達上限 / 磁碟剩餘不足	sys.master_files / 磁碟管理員

緊急處置三步走 (按優先序)

步驟 1：釋放空間讓業務恢復呼吸

-- A. 簡單模式：強制檢查點截斷
CHECKPOINT;
-- 或
DBCC FREEPROCCACHE; -- 釋放計劃快取間接協助

-- B. 完整/大量記錄模式：立即執行記錄備份 (無壓縮、無加密最快)
BACKUP LOG [DBName] TO DISK = 'NUL:';  -- 極端情況丟棄記錄，破壞備份鏈，謹慎使用！
-- 正規做法
BACKUP LOG [DBName] TO DISK = 'D:\Backup\DBName_Log_Emergency.trn' WITH COMPRESSION;

-- C. 臨時擴充記錄檔 (若磁碟尚有空間)
ALTER DATABASE [DBName] MODIFY FILE (NAME = 'DBName_Log', SIZE = 10GB, MAXSIZE = 50GB, FILEGROWTH = 1GB);

步驟 2：根因定阻與修正

長交易：KILL 並通知應用端重試
備份作業失敗：檢查 Agent 作業歷程、磁碟 I/O、網路路徑
鏡像/AG 同步延遲：檢查網路頻寬、次要複本硬體資源、暫停/移除問題複本
複寫讀取器卡住：重啟 Log Reader Agent、檢查發行項過濾條件

步驟 3：長效容量規劃

預先配置大小：依峰值每日記錄量 × 保留天數 (如 50GB/天 × 2 天 = 100GB)，一次配足，避免自動成長碎片
固定成長量：FILEGROWTH = 1GB (非百分比)，減少成長頻率
多記錄檔無意義：記錄檔循序寫入，多檔案不提升效能，僅增加管理複雜度

定期監控腳本：

SELECT 
    DB_NAME(database_id) AS DBName,
    (size * 8.0 / 1024) AS LogSizeMB,
    (FILEPROPERTY(name, 'SpaceUsed') * 8.0 / 1024) AS UsedMB,
    log_reuse_wait_desc
FROM sys.master_files
WHERE type_desc = 'LOG';