SQL Server日志文件收缩不了？实战案例分享：从排查到解决

运维的日常，就是和这些看似小却棘手的问题打交道!!!

天早上，开发团队反馈监控系统告警，数据库db1的日志文件已经把磁盘占满了。这已经是一个老问题，通常的解决方法是执行一波日志收缩操作。但这次，常规手段居然失效了！

1. 问题重现：常规操作失灵

通常我们会用以下命令来收缩日志：

ALTER DATABASE [db1] SET RECOVERY SIMPLE;
DBCC SHRINKFILE (N'db1_log', 1024);
ALTER DATABASE [db1] SET RECOVERY FULL;

但这次执行后，日志文件大小丝毫没有减少。作为一名经验丰富的DBA，是不是也意识到问题并不简单。

2. 排查过程：找出“罪魁祸首”

面对这种情况，我首先检查了日志无法重用的原因：

SELECT name, log_reuse_wait_desc 
FROM sys.databases 
WHERE name = 'db1';

查询结果显示 log_reuse_wait_desc的值为 REPLICATION 。

这有些奇怪，因为我知道这个数据库并没有配置复制。查阅资料后发现，这可能是之前未清理干净的复制元数据在作祟。

进一步确认活动事务情况：

DBCC OPENTRAN('db1');

果然，有活动事务阻塞了日志截断。

3. 解决方案：多管齐下

针对这个问题，我采取了以下措施：

• 清理复制元数据

EXEC sp_removedbreplication 'db1';

这个命令会清除数据库的复制信息，但请注意：如果数据库确实需要复制功能，则不能使用此方法。

• 处理活动事务

通过 DBCC OPENTRAN找到活动事务后，与开发团队确认，终止了那些长时间运行且不必要的事务。

如果是分布式事务或者使用了dblink,很可能会导致一直处于kill/rollback这种状态，且基本永远都不会自动回滚好（别问为什么，就是遇到过很多次），此时就需要考虑在业务低峰期或维护期重启数据库。

• 日志备份后截断

在完整恢复模式下，日志备份才是截断日志的正确方式：

-- 执行日志备份
BACKUP LOG [db1] TO DISK = N'D:\Backup\db1_Log.bak';
-- 然后再收缩
DBCC SHRINKFILE (N'db1_log', 1024);

如果无需备份日志，则直接改为simple模式后截断日志

-- 数据库恢复模式改为simple
ALTER DATABASE [db1] SET RECOVERY SIMPLE;

-- 截断数据库日志
DBCC SHRINKFILE (N'db1_log', 1024);

-- 恢复数据库恢复模式为full
ALTER DATABASE [db1] SET RECOVERY FULL;

用此种方式截断日志后建议做一次数据库全量备份。

之后可以查看一下各个文件的大小：

-- 查看所有数据文件和日志文件的大小及路径
SELECT DB_NAME(database_id) AS 数据库名, name AS 逻辑文件名, type_desc, physical_name, 
       size * 8.0 / 1024 AS 文件大小_MB,
       CASE WHEN type_desc = 'ROWS' THEN FILEPROPERTY(name, 'SpaceUsed') * 8.0 / 1024 ELSE NULL END AS 已用空间_MB,
       CASE WHEN type_desc = 'ROWS' THEN (size * 8.0 / 1024) - (FILEPROPERTY(name, 'SpaceUsed') * 8.0 / 1024) ELSE NULL END AS 剩余空间_MB
FROM sys.master_files;

• 预防措施：建立长效机制

问题解决后，我制定了以下预防措施，避免问题再次发生：

• 建立定期日志备份计划，避免日志无限增长
• 监控长时间运行的事务，设置告警机制
• 定期检查日志文件大小，防患于未然

4. 总结

通过这次排查，总结出日志无法收缩的几种常见原因及对策：

• 活动事务阻塞：使用 DBCC OPENTRAN检查并处理
• 复制问题：清理复制元数据或重新配置复制
• 缺少日志备份：在完整恢复模式下，必须定期备份日志
• 其他因素：如数据库镜像、快照创建等，需针对性处理

数据库日志管理是DBA日常工作的重要内容。与其等到日志文件撑爆磁盘再紧急处理，不如建立规范的监控和维护流程，从源头上解决问题。

希望这次实战经验对大家有所帮助！如果你有更好的解决方法或独特见解，欢迎在评论区交流。

【温馨提示】生产环境操作前请务必备份数据，本文仅供参考，操作风险自负。