错误 9002:由于 SQL Server 中AVAILABILITY_REPLICA错误消息,数据库的事务日志已满
本文可帮助你解决事务日志变大或SQL Server空间不足时发生的 9002 错误。
原始产品版本:SQL Server 2017、SQL Server 2016、SQL Server 2014、SQL Server 2012
原始 KB 编号: 2922898
症状
请考虑以下情况:
- 已在服务器上安装 Microsoft SQL Server 2012 或更高版本。
- SQL Server 实例是Always On可用性组环境中的主要副本 (replica) 。
- 事务日志文件的自动增长选项在 SQL Server 中设置。
在这种情况下,事务日志可能会变大,磁盘空间不足或超出主副本 (replica) 事务日志设置的 MaxSize 选项,并收到类似于以下内容的错误消息:
错误:9002,严重性:17,状态:9。 由于“AVAILABILITY_REPLICA”,数据库 '%.*ls' 的事务日志已满
原因
当主副本 (replica) 记录的更改尚未在辅助副本 (replica) 上强化时,会出现这种情况。 有关Always On环境中数据同步过程的详细信息,请参阅数据同步过程。
疑难解答
有两种方案可能导致可用性数据库中的日志增长,以及 'AVAILABILITY_REPLICA' log_reuse_wait_desc
:
方案 1:将记录的更改传送到辅助副本的延迟
当事务更改主副本 (replica) 中的数据时,这些更改将封装到日志记录块中,这些记录的块将传送并强化到辅助副本 (replica) 的数据库日志文件。 在将这些日志块传递到所有辅助副本中的相应数据库日志文件之前,主副本 (replica) 无法覆盖其自己的日志文件中的日志块。 将这些块传递到可用性组中的任何副本 (replica) 的任何延迟都会阻止截断主副本 (replica) 数据库中记录的更改,并导致其日志文件使用量增加。
有关详细信息,请参阅高网络延迟或低网络吞吐量导致主副本 (replica) 上的日志积累。
方案 2:重做延迟
强化到辅助数据库日志文件后,辅助副本 (replica) 实例中的专用重做线程会将包含的日志记录应用于相应的数据文件 () 。 主副本 (replica) 不能覆盖其自己的日志文件中的日志块,直到所有辅助副本中的所有重做线程都应用了包含的日志记录。
如果对任何辅助副本 (replica) 的重做操作无法跟上在该辅助副本 (replica) 上强化日志块的速度,则会导致主要副本 (replica) 的日志增长。 主副本 (replica) 只能截断并重用自己的事务日志,直到所有辅助副本 (replica) 的重做线程都已应用。 如果有多个辅助数据库,请比较
truncation_lsn
多个辅助数据库的动态管理视图列sys.dm_hadr_database_replica_states
,以确定哪个辅助数据库最延迟日志截断。可以使用Always On仪表板和
sys.dm_hadr_database_replica_states
动态管理视图来帮助监视日志发送队列和重做队列。 一些关键字段包括:字段 说明 log_send_queue_size
尚未到达辅助副本 (replica) log_send_rate
将日志记录发送到辅助数据库的速率。 redo_queue_size
尚未重做辅助副本 (replica) 日志文件中的日志记录量(以 KB (KB) 为单位)。 redo_rate
在给定辅助数据库上重做日志记录的速率,以 KB (kb) /秒为单位。 last_redone_lsn
在辅助数据库上重做的最后一个日志记录的实际日志序列号。 last_redone_lsn
始终小于last_hardened_lsn
。last_received_lsn
日志块 ID,标识托管此辅助数据库的辅助副本 (replica) 已接收所有日志块的点。 反映用零填充的日志块 ID。 它不是实际的日志序列号。 例如,对主副本 (replica) 执行以下查询,以报告副本 (replica) ,其上
truncation_lsn
限最早,并且是主节点可以在自己的事务日志中回收的上限:SELECT ag.name AS [availability_group_name] , d.name AS [database_name] , ar.replica_server_name AS [replica_instance_name] , drs.truncation_lsn , drs.log_send_queue_size , drs.redo_queue_size FROM sys.availability_groups ag INNER JOIN sys.availability_replicas ar ON ar.group_id = ag.group_id INNER JOIN sys.dm_hadr_database_replica_states drs ON drs.replica_id = ar.replica_id INNER JOIN sys.databases d ON d.database_id = drs.database_id WHERE drs.is_local=0 ORDER BY ag.name ASC, d.name ASC, drs.truncation_lsn ASC, ar.replica_server_name ASC
纠正措施可能包括但不限于以下内容:
- 确保辅助数据库没有资源或性能瓶颈。
- 确保 Redo 线程未在辅助位置被阻止。
lock_redo_blocked
使用扩展事件来确定何时发生此问题,以及哪些对象上阻止了重做线程。
解决方法
确定导致此问题的辅助数据库后,请尝试以下一个或多个方法来暂时解决此问题:
将数据库从有问题的辅助数据库的可用性组中取出。
注意
此方法将导致丢失辅助数据库的高可用性/灾难恢复方案。 将来可能需要再次设置可用性组。
如果重做线程经常被阻止,则通过将 副本 (replica) 的 参数
SECONDARY_ROLE
更改为ALLOW_CONNECTIONS
NO 来禁用Readable Secondary
该功能。注意
这将阻止用户读取辅助副本 (replica) 中的数据,这是阻塞的根本原因。 重做队列下降到可接受的大小后,请考虑再次启用该功能。
如果禁用了 自动增长 设置并且有可用的磁盘空间,请启用该设置。
如果已到达事务日志文件且有可用磁盘空间,请增大其 MaxSize 值。
如果当前事务日志文件已达到系统最大值 2 TB,或者在另一个可用卷上提供了额外的空间,则添加一个额外的事务日志文件。
更多信息
有关事务日志在SQL Server中意外增长或变满的原因的详细信息,请参阅排查完整事务日志 (SQL Server 错误 9002) 。
有关重做操作阻塞问题的详细信息,请参阅 AlwaysON - HADRON 学习系列:在辅助副本上阻止lock_redo_blocked/重做辅助角色。
有关基于AVAILABILITY_REPLICA log_reuse_wait列的详细信息,请参阅 可能会延迟日志截断的因素。
有关视图的详细信息
sys.dm_hadr_database_replica_states
,请参阅 sys.dm_hadr_database_replica_states (Transact-SQL) 。有关如何监视和排查未到达且未及时应用的已记录更改的详细信息,请参阅监视Always On可用性组的性能。
适用对象
- SQL Server 2012 Enterprise
- SQL Server 2014 Enterprise
- SQL Server 2014 Business Intelligence
- SQL Server 2014 Standard
- SQL Server 2016 Enterprise
- SQL Server 2016 Standard
- SQL Server 2017 企业版
- SQL Server 2017 标准 Windows