如何解决Sqlserver 2014 alwayson架构主节点执行alter table导致从节点的阻塞问题

蜗牛vps互联网技术资讯2021-11-292910

如何解决Sqlserver 2014 alwayson架构主节点执行alter table导致从节点的阻塞问题，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。

环境：
sqlserver 2014 alwayson架构
1个主节点
1个standby节点
6个只读节点

问题展现：
一台只读节点出现阻塞，主节点无法同步数据到这个只读节点。

问题原因：
在主节点执行了一个DDL语句，对一个表的字段类型进行了修改。
ALTER TABLE table1 ALTER COLUMN [Name] [NVARCHAR] (MAX)

此时正好只读库有一个关于table1表的长事务查询。
这个table1表的长事务查询阻塞了主节点执行ALTER TABLE table1 ALTER COLUMN [Name] [NVARCHAR] (MAX)对只读库的同步。
导致在只读节点上的table1表无法执行alter table操作。
这样导致所有的主节点的日志都无法从主节点同步到这个只读节点上。
而只读节点上所有后面对table1表的查询都被阻塞了。

查询主从同步脚本：
SELECT availability_mode_desc ,
role_desc ,
replica_server_name ,
last_redone_time ,
GETDATE() now ,
DATEDIFF(ms, last_redone_time, GETDATE()) diffMS
FROM ( ( sys.availability_groups AS ag
JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id
)
JOIN sys.dm_hadr_availability_replica_states AS ar_state ON ar.replica_id = ar_state.replica_id
)
JOIN sys.dm_hadr_database_replica_states dr_state ON ag.group_id = dr_state.group_id
AND dr_state.replica_id = ar_state.replica_id;

解决办法：
为了尽快解决此问题，最快的办法是杀掉那个只读节点上的长事务查询的session
让主节点的这个alter table的DDL操作可以同步到只读节点上，阻塞解决，同步也恢复正常。