单个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 开始引入的。

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

 
本文版权归作者所有，未经作者同意不得转载。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。