单个48TB大小MS SQL数据库备份导致日志文件无法截断的处理

不长叶子的树

SQL Server 版本：SQL Server 2019

背景
在一个48T大小的单数据库环境中，采用简单恢复模式，日志文件大小限制为600G。执行一次完整备份时，耗时超过12小时，导致日志文件无法截断并达到上限，后续事务无法正常写入，导致整个数据库不可用。



问题现象
LDF日志文件中的虚拟日志文件 (VLF) 全部为活动状态，导致日志无法正常截断。由于日志文件大小达到了600G的限制，后续的事务写入失败，导致数据库操作停滞。




排查思路
排查思路一般有下面几个：
1、大事务导致日志无法截断：可能是未提交的大事务阻止了日志的截断。
2、特殊环境：如复制 (Replication)、镜像 (Mirroring)、可用性组 (AG)、变更数据捕获 (CDC) 等场景下，备用端异常会导致主端无法截断日志。
3、未及时备份事务日志：在完整恢复模式下，未及时备份事务日志导致日志文件增长。
4、数据库恢复时间设置：修改过数据库恢复时间，可能导致checkpoint延迟，从而延长日志文件的截断时间。




排查步骤
按照下面思路进行逐一排查
1、确认数据库恢复模式：数据库为简单恢复模式，排除事务日志备份问题（思路3）。
2、检查运行环境：数据库为单机模式，排除复制、镜像、AG等场景（思路2）。
3、检查长时间事务：使用 `DBCC OPENTRAN` 检查，未发现长时间运行的事务，排除大事务问题（思路1）。
4、检查数据库恢复设置：`TARGET_RECOVERY_TIME` 和 `recovery interval` 为默认值，排除恢复时间问题（思路4）。
5、检查阻塞情况：未发现阻塞问题。
6、检查SQL Agent作业：除了完整备份外，未发现其他作业运行。
7、检查写入逻辑：与开发人员沟通得知，该数据库为日常批量数据写入，日志写入量超过400G。




问题原因分析
这里面我们需要从数据库完整备份的原理入手，解剖备份的细节，分析原因，一般来说，数据库完整备份分为两个阶段：
1、第一阶段：记录备份开始时的LSN，生成快照式备份。
2、第二阶段：快照备份结束后，记录最新的LSN，并将这两次LSN之间的事务日志写入备份。
由于数据库较大，备份时间超过12个小时，导致备份操作一直处于第一阶段，无法进入到第二阶段。这期间，日志文件中的事务无法截断，12小时内的事务量超过600GB，导致LDF日志文件被打爆。




优化建议
最后的解决方案是，开启SQL Server 2019的ADR（加速数据库恢复）功能，完整备份时候，日志文件只有少量增长，问题解决。
启用 ADR 的步骤
确保使用的是 SQL Server 2019 或 SQL Server 2022，因为 ADR 是从 SQL Server 2019 开始引入的。

1. --开启数据库的ADR功能
   
2. ALTER DATABASE [YourDatabaseName]
   
3. SET ACCELERATED_DATABASE_RECOVERY = ON;
   
4. GO
   
5. 
   
6. --检查 ADR 状态
   
7. SELECT name, is_accelerated_database_recovery_on
   
8. FROM sys.databases
   
9. WHERE name = 'YourDatabaseName';
   
10. 
    
11. --关闭 ADR
    
12. ALTER DATABASE [YourDatabaseName]
    
13. SET ACCELERATED_DATABASE_RECOVERY = OFF;
    
14. GO

复制代码

这里有如下几个优化思路：
1、多文件备份：将默认的单文件备份改为多文件备份，可以提升备份效率，缩短备份时间。（之前介绍过SQL Server的一个不显眼的功能备份文件分割）
2、升级SQL Server版本：升级到SQL Server 2019或SQL Server 2022，启用ADR（加速数据库恢复）功能，通过SLOG实现日志及时截断。
3、业务拆分：尽管不现实，但从业务上进行拆分也可以减少单一数据库的事务量。

---

总结

在SQL Server 2019中，一个48TB数据库因备份耗时过长，导致日志文件无法截断并达到上限，阻碍事务写入。
本文介绍了多种解决方案包括多文件分割备份、启用数据库ADR功能。
最终启用数据库ADR功能解决了问题。这里要注意的是，一定要尽量使用最新的数据库版本，例如SQL Server 2019或者SQL Server 2022，
保证能使用到最新功能，可以摆脱很多麻烦。




补充：数据库完整备份原理
在完整备份过程中，即使数据库处于简单恢复模式，备份依然会拷贝未提交事务的日志。对于长时间运行的事务，备份会包含足够的日志信息来撤销这些未提交的事务。
因此，即使数据库的MDF文件较小，日志文件（LDF）也可能会导致备份文件非常大。
这与MySQL的Xtrabackup的原理几乎是一样的，备份开始时生成一个开始LSN，结束时生成一个结束LSN，如果有未提交事务，MySQL8.0的undoxx文件会非常大



参考文章
https://learn.microsoft.com/en-u ... ew=sql-server-ver16
https://www.mssqltips.com/sqlser ... in-sql-server-2019/
https://medium.com/ricos-note/ac ... ecovery-a7f0d30b1e0

来自：https://www.cnblogs.com/lyhabc/p ... ckup-log-file-issue