SQLSERVER无法回收事务日志

郑全 · 发表于 2025-10-15 00:20:25

按理说日志回收的文章很多，但我今天遇到一个奇怪的问题，始终无法回收事务日志。
环境：
   WINDOWS2012+SQLSERVER 2008
问题现象：
   发现操作系统E盘1T,还剩180G,备份只有1月份和6月份的两个文件，两个文件加起来，400G。为什么最新的备份没有呢，而且这个备份怎么这么大？

问题分析：
   发现两个备份文件，单个200G,共400G，e:\database有510G，进一步分析，发现数据库中，数据库 NQPeis 的日志文件，达到460G,但该数据库本生，只有60G，这样计算，那么可以得出，SQLSERVER的磁盘空间，主要就是被这个 NQPeis 的日志文件占用，如果把这个日志文件空间回收，那么问题就可以解决了。

登录/注册后可看大图

5917568ee6d107fa88.png (174.63 KB, 下载次数: 105)

下载附件

2025-10-14 23:32 上传

登录/注册后可看大图

5476468ee6cf849637.png (188.53 KB, 下载次数: 115)

下载附件

2025-10-14 23:32 上传

于是根据以往经验，进行空间回收，但发现不管使用图形界面，还是命令执行回收后，空间没有变化。
直接回收：

ALTER DATABASE NQPeis SET RECOVERY SIMPLE
  GO use NQPeis
go
DBCC SHRINKFILE (NQPeis_Log,102400);
go

查看日志重用等待：
SELECT log_reuse_wait,log_reuse_wait_desc FROM sys.databases WHERE name='NQPeis'
发现是
   6 REPLICATION

   经过网上分析，一般是CDC开启了。
   检查CDC
   SELECT IS_CDC_ENABLED,
               CASE WHEN IS_CDC_ENABLED = 0 THEN 'CDC功能禁用'
                     ELSE 'CDC功能启用'
               END AS 描述
   FROM SYS.DATABASES
WHERE NAME = 'NQPeis';
   发现确实开启了CDC。经询问，没有使用CDC，于是关闭CDC功能
   USE NQPeis;
   GO
   EXEC sys.sp_cdc_disable_db;
   GO

   禁用后，再次去回收，依然日志不能缩小。

   再次查看
   SELECT log_reuse_wait,log_reuse_wait_desc FROM sys.databases WHERE name='NQPeis'；
   依然是 replication

   看一下日志信息
   dbcc loginfo(N'NQPeis')

登录/注册后可看大图

2031668ee71e10cc80.png (429.01 KB, 下载次数: 98)

下载附件

2025-10-14 23:53 上传

从图一的红色框可以看到，数据库的多个日志的状态都为2，也就是active状态。（如果为0 ，表示为inactive）。
这表明这些日志文件确实都在活动状态，一般而言，导致这种现象的原因主要有三种：长事务的运行、replication和mirroring延迟。
但当前这个库没有采用replication和mirroring，所以我初步锁定问题是因为长事务的运行导致。按照常规的方法，我只需分析下这个事务是否遇到阻塞、死锁等情况，然后给出对应的解决方案即可
经过检查，没有锁，阻塞。
SELECT * FROM sys.dm_exec_requests
WHERE blocking_session_id > 0;
这个库是一个遗留老库，基本处于查询状态，事务很少。

这个数据库并没有用作发布订阅，怎么会出现这个状态呢？
试图想确认一下这个数据库是否从一个发布订阅的数据库中还原过来的，尽管新的数据库并没有采用发布订阅，但数据库中发布订阅的一些配置选项还在，从而导致了数据库的误判，致使日志文件不断增大，但当事人说他接手时，已经就是这个样子，前面的情况也不太了解，虽然不太确定，但环境中很多发布订阅的库，试图通过sp_helppublication获取到@publication的名字，好直接删除发布，没有找到有效的记录。
于是，使用 EXEC sp_removedbreplication NQPeis 去移除残留发布信息，结果发现日志的状态发生了变化
dbcc loginfo(N'NQPeis')

登录/注册后可看大图

9372068ee75f624ba2.png (227.7 KB, 下载次数: 98)

下载附件

2025-10-15 00:10 上传

状态为2的已经很少了。
再去看日志重用原因
SELECT log_reuse_wait,log_reuse_wait_desc FROM sys.databases WHERE name='NQPeis'；
已经不是 replication
于是，再去进行空间回收
DBCC SHRINKFILE (NQPeis_Log,50);
发现有变化了，等了一会成功。
再去看日志文件大小，发现已经变小了。

登录/注册后可看大图

帐号		自动登录	找回密码
密码			注册