Alwayson运维常用sql

查询主从同步脚本

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;

监控日志发送队列脚本(在主副本上执行)

SELECT
        ag.name                AS ag_name          ,
        ar.replica_server_name AS ag_replica_server,
        dr_state.database_id   as database_id      ,
        dr_state.log_send_queue_size               ,
        is_ag_replica_local =
        CASE
        WHEN
                ar_state.is_local = 1
        THEN
                N'LOCAL'
        ELSE
                'REMOTE'
        END                                        ,
        ag_replica_role =
        CASE
        WHEN
                ar_state.role_desc IS NULL
        THEN
                N'DISCONNECTED'
        ELSE
                ar_state.role_desc
        END
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_statesdr_state
on
        ag.group_id         = dr_state.group_id
and     dr_state.replica_id = ar_state.replica_id;

监控日志应用队列脚本(在辅助副本上执行)

SELECT
        ag.name                AS ag_name          ,
        ar.replica_server_name AS ag_replica_server,
        dr_state.database_id   as database_id      ,
        dr_state.redo_queue_size                   ,
        is_ag_replica_local =
        CASE
        WHEN
                ar_state.is_local = 1
        THEN
                N'LOCAL'
        ELSE
                'REMOTE'
        END                                        ,
        ag_replica_role =
        CASE
        WHEN
                ar_state.role_desc IS NULL
        THEN
                N'DISCONNECTED'
        ELSE
                ar_state.role_desc
        END
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_statesdr_state
on
        ag.group_id         = dr_state.group_id
and     dr_state.replica_id = ar_state.replica_id;