在线修复Alwayson的重做日至过大

来源：这里教程网时间：2026-03-02 11:12:08 作者：

察看AG group的面板，察看AG组是否正常，如果面板显示正常执行2的步骤，面板显示异常的请根据异常提示来执行操作，本篇内容不涉及处理这些异常

执行以下语句

点击(此处)折叠或打开

select n.group_name,

n.replica_server_name,

n.node_name,

rs.role_desc,

CASE WHEN rs.is_local = 1 THEN N'LOCAL' ELSE 'REMOTE' END as is_local,

DB_NAME(drs.database_id) as 'database_name',

rs.connected_state_desc,

CASE WHEN drs.is_suspended=0 THEN 'RESUMED' ELSE

CASE WHEN drs.suspend_reason_desc='SUSPEND_FROM_USER' THEN '用户手动挂起数据移动'

WHEN drs.suspend_reason_desc='SUSPEND_FROM_PARTNER' THEN '在强制故障转移后挂起数据库副本'

WHEN drs.suspend_reason_desc='SUSPEND_FROM_REDO' THEN '在重做阶段中出错'

WHEN drs.suspend_reason_desc='SUSPEND_FROM_APPLY' THEN '在将日志写入文件时出错（请参阅错误日志）'

WHEN drs.suspend_reason_desc='SUSPEND_FROM_CAPTURE' THEN '在捕获主副本上的日志时出错'

WHEN drs.suspend_reason_desc='SUSPEND_FROM_RESTART' THEN '在重新启动数据库前挂起数据库副本（请参阅错误日志）'

WHEN drs.suspend_reason_desc='SUSPEND_FROM_UNDO' THEN '在撤消阶段中出错（请参阅错误日志）'

WHEN drs.suspend_reason_desc='SUSPEND_FROM_REVALIDATION' THEN '在重新连接时检测到了日志更改不匹配（请参阅错误日志）'

WHEN drs.suspend_reason_desc='SUSPEND_FROM_XRF_UPDATE' THEN '找不到公共日志点（请参阅错误日志）'

ELSE '' END

END as is_suspended, --是否数据库挂起及原因

drs.synchronization_state_desc,

drs.synchronization_health_desc,

ISNULL(drs.log_send_queue_size,0) as log_send_queue_size, --主数据库中尚未发送到辅助数据库的日志记录量 (KB)

ISNULL(drs.redo_queue_size,0) as redo_queue_size, --辅助副本的日志文件中尚未重做的日志记录量 (KB)

CASE WHEN ISNULL(drs.log_send_rate,0)=0 THEN 0

ELSE CAST(ISNULL(drs.log_send_queue_size,0)*1./ISNULL(drs.log_send_rate,0) AS DECIMAL(18,2))

END as log_send_need_time, --主数据库中日志记录发送完成需要时间（秒）

CASE WHEN ISNULL(drs.redo_rate,0)=0 THEN 0

ELSE CAST(ISNULL(drs.redo_queue_size,0)*1./ISNULL(drs.redo_rate,0) AS DECIMAL(18,2))

END as redo_need_time --辅助副本中日志记录重做完成需要时间（秒）

from sys.dm_hadr_availability_replica_cluster_nodes n

join sys.dm_hadr_availability_replica_cluster_states cs

on n.replica_server_name = cs.replica_server_name

join sys.dm_hadr_availability_replica_states rs

on rs.replica_id = cs.replica_id

join sys.dm_hadr_database_replica_states drs

on rs.replica_id=drs.replica_id

where rs.connected_state_desc<>'CONNECTED'

or drs.synchronization_health_desc<>'HEALTHY'

or drs.is_suspended<>0

or CASE WHEN ISNULL(drs.log_send_rate,0)=0 THEN 0 ELSE CAST(ISNULL(drs.log_send_queue_size,0)*1./ISNULL(drs.log_send_rate,0) AS DECIMAL(18,2)) END>100

确认
connected_state_desc=CONNECTED
is_suspended=RESUMED
synchronization_state_desc=SYNCHRONIZING
synchronization_health_desc=HEALTHY

这几个表示
ＡＧ组运行正常，同步也在继续，即可以排除网络和磁盘读写的问题
一般这些问题排除后可以定位为人为操作造成的

3. 用以下语句察看当前blocked sql

点击(此处)折叠或打开

;with cte as (select replace(hostname,' ','') as hostname ,''''+replace(program_name,' ','')+'''' as program_name

, loginame, db_name(a.dbid) AS DBname,spid,blocked,waittime/1000 as waittime,a.status,a.lastwaittype,a.cmd

,Replace(substring(b.text,1,340),'''','''') as sqlmessage,cpu

from sys.sysprocesses as a with(nolock)

cross apply sys.dm_exec_sql_text(sql_handle) as b

where a.blocked>0 and sql_handle<>0x0000000000000000000000000000000000000000

and waittime>2000 )

select replace(hostname,' ','') as hostname ,''''+replace(program_name,' ','')+'''' as program_name

, loginame, db_name(a.dbid) AS DBname,spid,blocked,waittime/1000 as waittime,a.status,a.lastwaittype,a.cmd

,Replace(substring(b.text,1,340),'''','''') as sqlmessage,cpu

from sys.sysprocesses as a with(nolock)

cross apply sys.dm_exec_sql_text(sql_handle) as b

where exists(select blocked from cte where cte.blocked=a.spid)

and not exists (select spid from cte where cte.spid=a.spid)

union all

select * from cte

４.一般来讲我们会看到用户大量的对数据库进行的操作，大致观察一下，和用户沟通后kill掉堵塞的进程。
用脚本可以直接kill所有进程，供参考，

点击(此处)折叠或打开

USE master;

DECLARE @SQL VARCHAR(MAX);

declare @i int;

set @i=3600 --大小可以先设置为redo_need_time(s)

SET @SQL=''

while @i>0

begin

SELECT @SQL=@SQL+'; KILL '+RTRIM(SPID)

FROM master..sysprocesses

WHERE dbid=DB_ID('datayesdb');

EXEC(@SQL);

set @i=@i-1

print @i

end

waitfor delay '0:0:1'

请注意该脚本只有在alwayson 两边延迟非常严重时才可以使用，一般redo_need_time(s) >3600可以酌情考虑
5.打开另一查询窗口用2的语句时时监控alwayson的状态
一般来讲，以下几个值都会慢慢减小，如果还在增大可能需要继续判断是否是其他问题，此篇不做讨论
log_send_queue_size
redo_queue_size
log_send_need_time
redo_need_time
6.当AG恢复正常，需要去重新启用之前被kill的job