首页 星云 工具 资源 星选 资讯 热门工具
:

PDF转图片 完全免费 小红书视频下载 无水印 抖音视频下载 无水印 数字星空

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

编程知识
2024年09月10日 07:00

单个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


 

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

From:https://www.cnblogs.com/lyhabc/p/18402109/sql-server-48tb-backup-log-file-issue
本文地址: http://www.shuzixingkong.net/article/1880
0评论
提交 加载更多评论
其他文章 .NET 9 的新亮点:AI就绪 ,拥抱她
.NET 9 即将发布 RC1, 今年初.NET 团队在发布.NET 9 Preview 1版本时写了一篇文章《我们对 .NET 9 的愿景》,其中特别提到了对AI的展望 .NET 9,我们致力于让 .NET 开发人员更轻松地将人工智能集成到他们现有的和新的应用程序中。 开发人员将找到用于使用 Op
逆向WeChat(六)
sniff微信小程序mojo数据包,通过嗅探mojo抓包https请求,打开小程序devtools。
逆向WeChat(六) 逆向WeChat(六) 逆向WeChat(六)
【LLM训练系列】从零开始训练大模型之Phi2-mini-Chinese项目解读
一、前言 本文主要是在复现和实践Phi2-mini-Chinese后,简要分析下Phi2-mini-Chinese这个项目,做一个学习实战总结。 原文发布于知乎:https://zhuanlan.zhihu.com/p/718307193,转载请注明出数。 Phi2-mini-Chinese简介 P
【LLM训练系列】从零开始训练大模型之Phi2-mini-Chinese项目解读 【LLM训练系列】从零开始训练大模型之Phi2-mini-Chinese项目解读 【LLM训练系列】从零开始训练大模型之Phi2-mini-Chinese项目解读
中文关键字检索分析-导出到csv或者excel-多文件或文件夹-使用python和asyncio和pandas的dataframe
1.02版本 把原来的tab一个个拼接成文件输出,改成pandas的dataframe 使用asyncio库来使用协程,但是测试下来速度好像是差不多的。可能速度太快了,没能很好的测出来差异。 原来的最初的代码是java版本的,现在用python重写一遍 java版本使用completableFutu
中文关键字检索分析-导出到csv或者excel-多文件或文件夹-使用python和asyncio和pandas的dataframe 中文关键字检索分析-导出到csv或者excel-多文件或文件夹-使用python和asyncio和pandas的dataframe
.NET 9 中 LINQ 新增的功能
LINQ介绍 语言集成查询 (LINQ) 是一系列直接将查询功能集成到 C# 语言的技术统称。数据查询历来都表示为简单的字符串,没有编译时类型检查或 IntelliSense 支持。此外,需要针对每种类型的数据源了解不同的查询语言:SQL 数据库、XML 文档、各种 Web 服务等。借助 LINQ,
.NET 9 中 LINQ 新增的功能 .NET 9 中 LINQ 新增的功能 .NET 9 中 LINQ 新增的功能
WiFi基础(三):802.11ac/ax/be 与 WiFi4、WiFi5、WiFi6、WiFi7
前面我们介绍了 802.11 b/g/n 的一些核心技术和基础概念,本章将介绍目前比较新的 WiFi5 和 WiFi6,以及在今年会发布的 WiFi7。
WiFi基础(三):802.11ac/ax/be 与 WiFi4、WiFi5、WiFi6、WiFi7 WiFi基础(三):802.11ac/ax/be 与 WiFi4、WiFi5、WiFi6、WiFi7 WiFi基础(三):802.11ac/ax/be 与 WiFi4、WiFi5、WiFi6、WiFi7
喜欢干净简洁音乐播放器的朋友看过来
大家好,我是晓凡。 不少程序员小伙伴都喜欢边听音乐边敲代码,尤其在一个嘈杂的环境中,一个好的想法、好的思路可能就因为一瞬间的干扰就没了。 这时,如果耳机一戴上,听着音乐能更好的集中注意力;遇到bug也能临危不乱,想出更好的解决办法; 网易云音乐,算是一个相对简洁、有趣的播放器了。 不过近些年,各种花
喜欢干净简洁音乐播放器的朋友看过来 喜欢干净简洁音乐播放器的朋友看过来 喜欢干净简洁音乐播放器的朋友看过来
军工厂电气工程师到知名互联网公司程序员,我吃饭的家伙有哪些
大家好,我是欧阳。今年刚好是欧阳三十而立之年,虽然没有立起来。这篇文章来聊聊我从一名军工厂电气工程师到某知名互联网公司程序员,这期间我吃饭的家伙都有哪些。 军工厂期间 欧阳大学读的是“电气工程及其自动化专业”,毕业后进入了老家的一个军工厂,成为了一名电气工程师。军工厂的位置是在一个镇上,和“繁华”这
军工厂电气工程师到知名互联网公司程序员,我吃饭的家伙有哪些 军工厂电气工程师到知名互联网公司程序员,我吃饭的家伙有哪些 军工厂电气工程师到知名互联网公司程序员,我吃饭的家伙有哪些