数据库集群可用性组同步情况查询

SELECT 
ar.replica_server_name 可用性组, 
ar.availability_mode_desc, 
DB_NAME(dbr.database_id) AS 数据库名称 , 
CASE dbr.database_state WHEN 0 THEN '联机' WHEN 1 THEN '正在还原' WHEN 2 THEN '正在恢复' WHEN 3 THEN '恢复挂起' WHEN 4 THEN '可疑' WHEN 5 THEN '紧急' WHEN 6 THEN '脱机' END 数据库状态, 
CASE dbr.synchronization_state WHEN 0 THEN '不同步' WHEN 1 THEN '正在同步' WHEN 2 THEN '已同步' WHEN 3 THEN '正在还原' WHEN 4 THEN '正在初始化' END 数据移动状态, 
CASE dbr.synchronization_health WHEN 0 THEN '不正常' WHEN 1 THEN '部分正常' WHEN 2 THEN '正常' END [可用性副本同步运行状态],
CASE dbr.is_commit_participant WHEN 0 THEN '事务提交未同步' WHEN 1 THEN '事务提交同步' END 事务提交状态,
CASE dbr.is_suspended WHEN 0 THEN '已恢复' WHEN 1 THEN '已挂起' END 数据库状态1,
CASE dbr.suspend_reason WHEN 0 THEN '用户手动挂起的收据移动' WHEN 1 THEN '在强制故障转移后挂起数据库副本' WHEN 2 THEN '在重做阶段中出错' WHEN 3 THEN '在捕获主副本上的日志时出错' WHEN 4 THEN '在将日志写入文件时出错（请参阅错误日志）' WHEN 5 THEN '在重新启动数据库前挂起数据库副本（请参阅错误日志）' WHEN 6 THEN '在撤消阶段中出错（请参阅错误日志）' WHEN 7 THEN '在重新连接时检测到了日志更改不匹配（请参阅错误日志）' WHEN 8 THEN '找不到公共日志点（请参阅错误日志）' END 已挂起状态的原因,
ISNULL(CASE dbr.redo_rate WHEN 0 THEN 0 ELSE CAST(dbr. redo_queue_size AS FLOAT) / dbr. redo_rate END, 0) AS [redo_delay(s)] , 
dbr.redo_queue_size AS [redo_queue_size(KB)] , dbr.redo_rate,
dbr.log_send_queue_size AS [log_send_queue(KB)] , 
dbr.log_send_rate, 
ISNULL(CASE dbr.log_send_rate WHEN 0 THEN 0 ELSE CAST(dbr. log_send_queue_size AS FLOAT) / dbr. log_send_rate END, 0) AS [log_delay(s)]
FROM [master]. sys.availability_replicas AS AR 
INNER JOIN [master].sys.dm_hadr_database_replica_states AS dbr ON ar.replica_id = dbr.replica_id
WHERE ISNULL(CASE dbr.redo_rate WHEN 0 THEN 0 ELSE CAST(dbr. redo_queue_size AS FLOAT) / dbr. redo_rate END, 0) >1
ORDER BY ar.replica_server_name, 数据库名称