SQL Server 事务日志暴涨排查实战：从 log_reuse_wait_desc 到 VLF 治理

dbaai

SQL Server 事务日志暴涨排查实战：从 log_reuse_wait_desc 到 VLF 治理

一、具体的问题

周一早上刚到公司，监控就炸了：一台跑订单库的 SQL Server 实例，数据盘剩余空间从 40% 直接掉到 3%。上去一看，OrderDB 的日志文件 OrderDB_log.ldf 已经从平时的 2GB 涨到 121GB，而数据文件只有 30GB —— 日志比数据大了四倍。

紧接着业务侧反馈：下单接口大面积报错，错误号 9002：

2. Msg 9002, Level 17, State 2
3. The transaction log for database 'OrderDB' is full due to 'LOG_BACKUP'.

复制代码

这类问题在 DBA 日常里非常高频，但也非常容易被"野路子"处理掉。我见过最多的三种错误操作：

• 直接停掉 SQL Server 服务，把 ldf 文件删了再启动 —— 数据库起不来，或者拉起后进入 RECOVERY_PENDING，只能紧急还原。
  
• 改成简单恢复模式，收缩日志，再改回完整模式 —— 日志是没了，但日志链（log chain）彻底断了，上一次完整备份之后的所有日志备份全部失效，时间点恢复能力归零。如果这时候真出事，只能恢复到上次全备。
  
• 不做任何判断直接 DBCC SHRINKFILE —— 收缩完第二天又涨回来，因为根本没找到"为什么日志不能被截断"的原因。
  

正确做法只有一条：先搞清楚是谁在占着日志不让复用（log_reuse_wait_desc），再对症下药，最后才谈收缩。 顺序反了就是白干。

二、核心原理

1. 日志是循环写的，不是追加写的

事务日志在物理上被切成一段一段的 VLF（Virtual Log File，虚拟日志文件）。新日志记录写到当前活跃的 VLF，写满了就跳到下一个。当最后一个 VLF 也写满时，引擎会绕回开头，尝试复用最前面的 VLF。

但复用有个前提：这个 VLF 必须已经被"截断"（truncated）。截断是指把 VLF 标记为可覆盖，文件的物理大小一点没变。

所以这里有两个完全不同的概念，很多新手会混：

动作	作用	文件大小变化
截断（truncate）	把已备份/不再需要的 VLF 标记为可复用	不变
收缩（shrink）	把文件尾部的空间还给操作系统	变小

日志暴涨的根因是"截断不了"，不是"没收缩"。 收缩只是善后。

2. log_reuse_wait_desc：一句话告诉你卡在哪

sys.databases 里的 log_reuse_wait_desc 字段，就是引擎给出的"日志为什么不能复用"的直接答案。常见取值：

取值	含义	常见原因
NOTHING	可以复用，状态正常	日志其实没满
LOG_BACKUP	需要先做日志备份	完整/大容量日志模式下没配日志备份作业
ACTIVE_TRANSACTION	有未提交的长事务	忘提交、隐式事务、大批量操作
CHECKPOINT	等待检查点	简单模式下极少见，通常短暂
AVAILABILITY_REPLICA	日志没送到 AlwaysOn 次要副本	副本断连、网络、redo 积压
DATABASE_MIRRORING	镜像未同步	镜像挂起或延迟大
REPLICATION	日志未分发到订阅端	分发代理停了、订阅过期
LOG_SCAN	有日志读取操作（备份/还原/复制）未完成	正在做还原或日志读取
OLDEST_PAGE	与加速数据库恢复（ADR）相关	SQL 2019+

看到这个值，问题基本就定位了一半。

3. 恢复模式决定截断条件

• SIMPLE（简单）：检查点发生时自动截断，不需要日志备份。代价是无法做时间点恢复，只能恢复到上次全备/差异备。
  
• FULL（完整）：必须做 日志备份（BACKUP LOG） 才能截断。只有做了日志备份，才能做任意时间点恢复（PITR）。
  
• BULK_LOGGED（大容量日志）：介于两者之间，大容量操作最小日志，但该段日志备份要带走整个数据区。
  

生产 OLTP 库一律用 FULL + 定期日志备份（常见 15 分钟或 5 分钟一次）。改恢复模式是最不该动的那根弦。

4. VLF 过多：一个被低估的性能杀手

日志文件的自动增长设置如果用了默认的 10%，一个 100GB 的日志文件在增长过程中会产生几千个 VLF。VLF 太多会导致：

• 数据库启动、还原、附加变慢（要枚举所有 VLF）
  
• 日志备份和 truncate 变慢
  
• 事务复制/CDC 的日志读取器延迟
  

社区经验值：单个 VLF 建议 512MB～1GB 之间，VLF 总数控制在 几百个以内（超过 1000 就该治理了）。每次增长不超过 8GB，这样一次增长最多新增 16 个 VLF。

三、实例参考（动手步骤）

下面这套步骤我在生产上跑过很多次，按顺序执行即可。全程以 OrderDB 为例。

步骤 1：确认状态和恢复模式

2. SELECT name,
3.        recovery_model_desc,
4.        log_reuse_wait_desc,
5.        state_desc
6. FROM sys.databases
7. WHERE database_id > 4
8. ORDER BY name;

复制代码

本次输出：

2. name     recovery_model_desc  log_reuse_wait_desc  state_desc
3. OrderDB  FULL                 LOG_BACKUP           ONLINE

复制代码

LOG_BACKUP 说明：完整模式下没人做日志备份。一查 Agent 作业，日志备份作业上周因为磁盘满被禁用后就没人打开了。

步骤 2：看日志实际用了多少

2. DBCC SQLPERF(LOGSPACE);

复制代码

输出：

2. Database Name  Log Size (MB)  Log Space Used (%)  Status
3. OrderDB        124416         99.87               0

复制代码

注意区分"文件大小"和"已用比例"。如果 Used% 很低但文件很大，说明是历史增长留下的空壳，截断已经正常，只需收缩。

步骤 3A：LOG_BACKUP —— 补一次日志备份

2. BACKUP LOG [OrderDB]
3.   TO DISK = N'D:\bak\OrderDB\OrderDB_LOG_20260919_0800.trn'
4.   WITH COMPRESSION, STATS = 10;

复制代码

跑完再查 log_reuse_wait_desc，会变成 NOTHING。但这只是临时止血，必须同时把日志备份作业恢复起来，否则过几个小时又满了。

顺手确认日志链是否完整（改过恢复模式的话这里会露馅）：

2. SELECT TOP (20)
3.        s.database_name,
4.        s.backup_start_date,
5.        s.type,
6.        s.first_lsn,
7.        s.last_lsn
8. FROM msdb.dbo.backupset s
9. WHERE s.database_name = N'OrderDB'
10. ORDER BY s.backup_start_date DESC;

复制代码

type 取值：D 全备、I 差异、L 日志。如果某次全备之后的 L 记录中间断过一次（比如有人切成 SIMPLE 又切回来），这条链就废了。

步骤 3B：ACTIVE_TRANSACTION —— 抓长事务

如果 log_reuse_wait_desc 是 ACTIVE_TRANSACTION，先拿到最老的活动事务：

2. DBCC OPENTRAN('OrderDB');

复制代码

输出会给出 SPID 和开始时间，再反查会话详情：

2. SELECT s.session_id,
3.        s.host_name,
4.        s.program_name,
5.        s.login_name,
6.        t.transaction_begin_time,
7.        DATEDIFF(MINUTE, t.transaction_begin_time, GETDATE()) AS running_minutes,
8.        c.client_net_address,
9.        txt.text AS last_sql
10. FROM sys.dm_tran_active_transactions t
11. JOIN sys.dm_tran_session_transactions st ON st.transaction_id = t.transaction_id
12. JOIN sys.dm_exec_sessions s ON s.session_id = st.session_id
13. LEFT JOIN sys.dm_exec_connections c ON c.session_id = s.session_id
14. OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) txt
15. WHERE DATEDIFF(MINUTE, t.transaction_begin_time, GETDATE()) > 5
16. ORDER BY running_minutes DESC;

复制代码

本次抓到的真凶是一个 ETL 程序：program_name 是 SQLAgent - TSQL JobStep，last_sql 是一条 DELETE FROM t_order_detail WHERE create_time < '2024-01-01'，已经跑了 6 小时。

处理方式（按优先级）：

• 能改应用就改应用 —— 大删除改成分批，每批 5000 行并显式提交：
  

2. WHILE 1 = 1
3. BEGIN
4.     DELETE TOP (5000) FROM t_order_detail
5.     WHERE create_time < '2024-01-01';
7.     IF @@ROWCOUNT = 0 BREAK;
8.     WAITFOR DELAY '00:00:00.200';
9. END

复制代码

• 紧急止血才 KILL <spid>，但要清楚：回滚时间约等于已经跑的时间，6 小时的事务可能要回滚 6 小时，期间日志照样在涨。
  

顺带排查隐式事务这个大坑：

2. SELECT session_id,
3.        CASE WHEN SESSIONPROPERTY('IMPLICIT_TRANSACTIONS') = 1
4.             THEN '隐式事务开启' ELSE '正常' END AS implicit_flag,
5.        open_transaction_count,
6.        status
7. FROM sys.dm_exec_sessions
8. WHERE is_user_process = 1 AND open_transaction_count > 0;

复制代码

有些 ODBC/JDBC 驱动默认开 IMPLICIT_TRANSACTIONS，程序里只 SELECT 也会留着事务不提交，日志就一直截断不了。这是非常经典的疑难杂症。

步骤 3C：AVAILABILITY_REPLICA —— 查 AlwaysOn 同步

2. SELECT ag.name AS ag_name,
3.        ar.replica_server_name,
4.        drs.synchronization_state_desc,
5.        drs.log_send_queue_size,
6.        drs.redo_queue_size,
7.        drs.last_redone_time
8. FROM sys.dm_hadr_database_replica_states drs
9. JOIN sys.availability_replicas ar ON ar.replica_id = drs.replica_id
10. JOIN sys.availability_groups ag ON ag.group_id = drs.group_id
11. WHERE drs.database_id = DB_ID(N'OrderDB');

复制代码

log_send_queue_size 持续大于 0 说明日志发不出去（网络/副本挂了/副本磁盘满）；redo_queue_size 大说明副本重做跟不上（副本有阻塞或资源不足）。把副本修好，日志自然能截断。不要为了让主库活下去就把库踢出 AG。

步骤 3D：REPLICATION —— 查分发

2. -- 在发布库上执行，看最老的未分发事务
3. DBCC OPENTRAN('OrderDB');

复制代码

输出里如果出现 Replicated Transaction Information，说明复制的日志读取器（Log Reader）没跟上。去分发服务器上确认 Log Reader Agent 是否在跑、是否报错。订阅端过期的话，日志会一直挂着不释放。

步骤 4：VLF 体检

SQL Server 2016 SP2 之前：

2. DBCC LOGINFO('OrderDB');   -- 返回的行数就是 VLF 数量

复制代码

2016 SP2 之后用 DMV 更清爽：

2. SELECT COUNT(*) AS vlf_count,
3.        MAX(vlf_size_mb) AS max_vlf_mb,
4.        MIN(vlf_size_mb) AS min_vlf_mb
5. FROM sys.dm_db_log_info(DB_ID(N'OrderDB'));

复制代码

本次结果：VLF 4217 个，最小的只有 1.2MB，最大的 8GB —— 典型的历史遗留，早期按 10% 增长留下来的。

步骤 5：治理 VLF（备份 → 收缩 → 一次性增长）

顺序不能反，且要在业务低峰做：

2. -- 1) 先确保日志可截断并做一次日志备份
3. BACKUP LOG [OrderDB] TO DISK = N'D:\bak\OrderDB\OrderDB_tail.trn' WITH COMPRESSION;
5. -- 2) 收缩到目标初始大小（这里先收到 1GB）
6. DBCC SHRINKFILE (N'OrderDB_log', 1024);
8. -- 3) 一次增长到位，同时把增长改成固定值而不是百分比
9. ALTER DATABASE [OrderDB]
10.   MODIFY FILE (NAME = N'OrderDB_log', SIZE = 8192MB, FILEGROWTH = 1024MB);

复制代码

如果目标大小远大于 8GB（比如要 64GB），分多次增长，每次不超过 8GB，避免一次生成过多 VLF：

2. ALTER DATABASE [OrderDB] MODIFY FILE (NAME = N'OrderDB_log', SIZE = 16384MB);
3. ALTER DATABASE [OrderDB] MODIFY FILE (NAME = N'OrderDB_log', SIZE = 24576MB);
4. ALTER DATABASE [OrderDB] MODIFY FILE (NAME = N'OrderDB_log', SIZE = 32768MB);

复制代码

收尾再验一次：

2. SELECT COUNT(*) FROM sys.dm_db_log_info(DB_ID(N'OrderDB'));
3. DBCC SQLPERF(LOGSPACE);

复制代码

步骤 6：本地复现（想彻底理解的话值得做一遍）

2. CREATE DATABASE [LogLab];
3. GO
4. ALTER DATABASE [LogLab] SET RECOVERY FULL;
5. GO
6. USE [LogLab];
7. CREATE TABLE t (id INT IDENTITY, c CHAR(8000));
8. GO
9. -- 跑几轮，中间不做日志备份
10. INSERT INTO t(c) SELECT TOP (20000) 'x' FROM sys.all_objects a CROSS JOIN sys.all_objects b;
11. GO 5
12. -- 每次跑完观察
13. SELECT log_reuse_wait_desc FROM sys.databases WHERE name = 'LogLab';
14. DBCC SQLPERF(LOGSPACE);

复制代码

你会看到 log_reuse_wait_desc 稳定停在 LOG_BACKUP。然后做一次 BACKUP LOG，它立刻变回 NOTHING。这个实验比看十篇文章都管用。

前后对比

指标	治理前	治理后
日志文件大小	121.5 GB	8 GB（初始），日常峰值 3.2 GB
日志已用比例	99.87%	12%～35% 波动
VLF 数量	4217	44
日志备份作业	已禁用 7 天	每 15 分钟一次，含校验告警
数据库重启恢复耗时	约 190 秒	约 6 秒
9002 报错次数/周	4 次	0 次
最长活动事务	6 小时 12 分	最长 42 秒（大删除改分批后）

几个容易踩的坑

• 收缩不是维护动作，不要放进定期作业。 反复收缩-增长会加重 VLF 碎片，还会引发文件级碎片。
  
• 日志备份和全备放在同一块盘，盘满了两个都做不了，等于没有备份。
  
• "先切 SIMPLE 收缩再切回 FULL"之后，必须立刻做一次全备（或差异备），否则新的日志链没有起点，日志备份会直接失败。
  
• tempdb 的日志满了处理方式不同，通常是把 tempdb 拆成多个等大数据文件、取消自动增长百分比。
  

四、实操检查清单

• [ ] 建库时恢复模式按需设定：生产 OLTP 一律 FULL，并在上线当天配好日志备份作业
  
• [ ] 日志备份频率按 RPO 定：常见 15 分钟，核心库 5 分钟；备份落盘与数据盘分离
  
• [ ] 日志文件的自动增长改为固定值（512MB～1024MB），绝不使用百分比
  
• [ ] 每次增长不超过 8GB；新建/治理时一次性增长到位并分数次执行
  
• [ ] 定期巡检 VLF 数量（目标 < 1000，理想几百），纳入月度健康检查脚本
  
• [ ] 对 log_reuse_wait_desc <> 'NOTHING' 且持续超过 30 分钟的情况配置告警
  
• [ ] 长事务监控：open_transaction_count > 0 且持续 > 5 分钟的会话要能告警到应用负责人
  
• [ ] 应用侧规范：禁止长事务内的交互式等待；大批量 DML 改分批 + 显式提交
  
• [ ] 检查驱动连接串，避免 IMPLICIT_TRANSACTIONS 被默认开启（尤其老 ODBC/JDBC）
  
• [ ] AlwaysOn/镜像/复制环境，把副本与分发代理健康度纳入同一套告警，避免它们拖垮主库日志
  
• [ ] 禁止把 DBCC SHRINKFILE 放进定期维护作业；收缩只在 VLF 治理时人工执行
  
• [ ] 任何恢复模式变更后，立刻补一次全备或差异备，重建日志链
  
• [ ] 每季度做一次还原演练，验证日志链与 PITR 真实可用（备份没验证过等于没备份）
  
• [ ] 磁盘剩余空间告警阈值设为 20%，不要把 5% 当作第一道防线
  

---

以上操作和命令在 SQL Server 2016/2019/2022 上均适用，sys.dm_db_log_info 需要 2016 SP2 及以上版本。

—— dbaai