Sqlserver查询alwayson同步情况脚本（2）

SELECT ag.name AS ag_name, ar.replica_server_name, db_name(dr_state.database_id), is_ag_replica_local = CASEWHEN ar_state.is_local = 1 THEN N'LOCAL'ELSE 'REMOTE'END , ag_replica_role = CASEWHEN ar_state.role_desc IS NULL THEN N'DISCONNECTED'ELSE ar_state.role_descEND, dr_state.last_redone_time, dr_state.last_hardened_lsn, dr_state.last_hardened_time, datediff(ms,last_hardened_time, getdate()) as 'seconds behind primary'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_idorder by last_hardened_lsn desc,last_hardened_time last_hardened_time:这个时间点表示AlwaysOn主节点的事务日志传到从节点的时间。last_redone_time:这个时间点表示 AlwaysOn主节点的事务日志传到从节点后完成redo的时间（完成落盘），完成redo表示从节点已经完全同步了主节点的事务日志。