大事务导致数据库恢复时间长

背景

客户的一套系统从凌晨开始出现运行缓慢，重启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+客户，覆盖互联网、市政、交通、电信、医疗、教育、电力、制造业等各个领域。