SQL Server 2012事务日志持续填满，自动清理作业问题咨询

看你已经搭了自动清理日志的作业，先给你拆解下当前的逻辑，再针对日志一直涨的核心问题给点实用建议：

先唠唠你现在的清理作业 #

你设置的Cleanup DB Logs作业分两步：

• 步骤1：执行事务日志备份，代码如下：

  BACKUP LOG [XXXX_Database] TO DISK = N'I:\XXX_Database.Trn' WITH NOFORMAT, NOINIT, NAME = N'XXX_Database-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
  

• 步骤2：执行日志收缩再加一次事务日志备份，成功后退出

为啥日志还是蹭蹭涨？ #

首先得搞明白：事务日志备份是给SQL Server发信号“这部分日志已经备份完，可以重用了”，而收缩是把这些空出来的空间还给系统。但日志一直涨，大概率是这几个坑没踩对：

1. 有“赖着不走”的长事务 #

如果某个事务启动后一直不提交（比如应用程序卡了、查询跑崩没回滚），SQL Server不敢动这部分日志，只能一直往日志文件里加内容，自然就撑满了。

• 排查长事务的方法：跑下面的SQL，找出持续超30分钟的事务：

  SELECT 
      transaction_id,
      name AS transaction_name,
      transaction_begin_time,
      DATEDIFF(MINUTE, transaction_begin_time, GETDATE()) AS 持续时间_分钟
  FROM sys.dm_tran_active_transactions
  WHERE transaction_begin_time < DATEADD(MINUTE, -30, GETDATE());
  

2. 日志备份频率太低 #

如果数据库处于完整恢复模式，只有做完日志备份，SQL Server才会截断日志。要是你的作业半天跑一次，两次备份之间产生的日志量直接爆了现有日志文件的空间，那肯定会持续增长。

• 建议：根据数据库的写入量调整频率，写得勤的库（比如电商、业务系统）可以设成15-30分钟一次备份，写得少的可以1小时一次。

3. 日志收缩的姿势不对 #

你步骤2里又备份又收缩，其实步骤1已经做过备份了，步骤2的备份纯属冗余。而且频繁收缩日志会搞出很多碎片，反而拖慢性能，下次有大量写入时日志又会猛涨，陷入“收缩-膨胀-再收缩”的死循环。

• 正确收缩逻辑：只有确认有大量空闲空间（比如刚做完全量备份+日志备份，也没活跃事务）的时候再收缩，而且别缩得太小，得留够日常写入的余量。
• 正确的收缩语句：先查日志文件的逻辑名，再执行收缩：

  -- 查看日志文件逻辑名称
  SELECT name FROM sys.database_files WHERE type_desc = 'LOG';
  -- 收缩到指定大小（示例为1GB，根据实际情况调整）
  DBCC SHRINKFILE (N'你的日志文件逻辑名', 1024);
  

4. 恢复模式选得不对 #

要是你的数据库不需要精确到某个时间点恢复，其实可以切换到简单恢复模式，这样SQL Server会自动截断日志（做完检查点后），不用手动备份日志。但注意：切换到简单模式后，没法恢复到故障前的状态，适合对恢复要求不高的场景（比如测试库）。

• 切换恢复模式的语句：

  ALTER DATABASE [XXXX_Database] SET RECOVERY SIMPLE;
  

给你的作业优化建议 #

调整下作业步骤，去掉冗余操作，逻辑更顺畅：

1. 步骤1：保留你现在的日志备份语句即可
2. 步骤2：先检查日志空闲空间，达到阈值再执行收缩（别天天跑，比如一天一次就行）

   -- 先查看日志使用情况
   SELECT 
       name AS 日志文件名,
       size/128.0 AS 当前大小_MB,
       FILEPROPERTY(name, 'SpaceUsed')/128.0 AS 已使用_MB,
       (size - FILEPROPERTY(name, 'SpaceUsed'))/128.0 AS 空闲空间_MB
   FROM sys.database_files
   WHERE type_desc = 'LOG';
   
   -- 示例：如果空闲超过5GB，收缩到2GB，根据实际情况修改数值
   DBCC SHRINKFILE (N'你的日志文件逻辑名', 2048);
   

最后提个醒：日志增长是SQL Server正常工作的表现，别执念于把日志缩到最小。合理设置日志文件的初始大小（比如按日常峰值的200%设置），自动增长设固定值（比如每次涨1GB，别按百分比），才是长期解决问题的根本。

内容的提问来源于stack exchange，提问作者Smartiot