背景
客户的一套系统从凌晨开始出现运行缓慢,重启SQL Server服务后一个主要的数据库一直处在正在恢复的状态,多次重启SQL Server服务和服务器无果后请我们协助处理。
现象
在SSMS中看到数据库是正在恢复的状态,而且不能被访问。
分析
启动SQL Server服务时数据库恢复要经过分析、重做和撤销3个阶段,在阶段2完成后数据库才能提供访问。如果某个阶段运行时间长,在日志里面会记录进度。
查找日志,发现从8:31:45开始阶段1,8:34:30开始阶段2,从进度上看现在已经完成了28%。确保磁盘空间充足后,剩下的工作就是查看进度,等待完成。
9:10:52,看到进度为99%的日志。9:11:11,阶段2完成。在完成阶段2后,数据库就可以访问了。
直到15:21:14时才看到阶段3完成的日志,三个阶段共花费7个小时的时间。
通过日志记录看到阶段3的时间非常长,说明在停止SQL Server服务时有特别大的事务在运行。通过SQL专家云,在活动会话中找到会话157,从0:36:38时开始执行一个INSERT语句,到4:14:01停止SQL Server服务时还没有执行完,期间造成了大量的阻塞,日志文件从几个G增长到100G。
再来分析为什么恢复数据库要这么长时间,根据数据库恢复的流程,忽略掉阶段1的时间,阶段2和阶段3都是要从最早的未提交事务的时间点开始分析事务日志,也就是要从0:36:38开始,要分析处理100G的日志。
总结
大的INSERT事务(分析后得知因为条件写错了,要插入6000万条数据)执行期间造成了大量的阻塞,影响了系统的其它功能,客户接到故障报警后没有仔细分析原因就直接重启了SQL Server服务。重启后数据库的恢复时间非常长,由于客户不熟悉原理,每次启动服务后等待十几分钟看数据库状态不对便再次重启,如此反复多次,耽误了很长的时间。
SQL Server作为一个成熟的数据库,在每个步骤都会记录下详细的日志,我们要养成看日志分析问题的习惯。另外,从SQL Server 2016开始,增加了数据库恢复进度的扩展事件,可以分析的更详细。具体参考“https://learn.microsoft.com/zh-cn/archive/blogs/sql_server_team/new-extended-events-for-database-recovery-progress”。
大事务导致日志文件增大,磁盘空间撑爆,事务回滚时间长,SQL Server服务异常终止等问题,要尽量避免大事务。
大事务导致的回滚时间长或者异常终止后重启SQL Server服务时数据库恢复时间长是一个非常困扰的问题。在SQL Server 2019新推出的“加速数据库恢复”功能就是解决这个痛点的,但是不成熟,开启这个功能又导致了数据文件增长过大等其它的问题,在SQL Server 2022版本中进行了改进。
详细资料参考“https://learn.microsoft.com/zh-cn/sql/relational-databases/accelerated-database-recovery-concepts?view=sql-server-ver16”。
北京格瑞趋势科技有限公司是聚焦于数据服务的高新技术企业,成立于2008年,创始团队及核心技术人员来自微软和雅虎。微软数据平台合作伙伴,卫宁健康数据平台战略合作伙伴。通过产品+服务双轮驱动的业务模式,14年间累计服务4000+客户,覆盖互联网、市政、交通、电信、医疗、教育、电力、制造业等各个领域。