单个48TB大小SQL Server数据库备份导致日志文件无法截断
单个48TB大小SQL Server数据库备份导致日志文件无法截断
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 开始引入的。
--开启数据库的ADR功能
ALTER DATABASE [YourDatabaseName]
SET ACCELERATED_DATABASE_RECOVERY = ON;
GO
--检查 ADR 状态
SELECT name, is_accelerated_database_recovery_on
FROM sys.databases
WHERE name = 'YourDatabaseName';
--关闭 ADR
ALTER DATABASE [YourDatabaseName]
SET ACCELERATED_DATABASE_RECOVERY = OFF;
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-us/sql/relational-databases/accelerated-database-recovery-concepts?view=sql-server-ver16
https://www.mssqltips.com/sqlservertip/5971/accelerated-database-recovery-in-sql-server-2019/
https://medium.com/ricos-note/accelerated-database-recovery-a7f0d30b1e0
本文版权归作者所有,未经作者同意不得转载。