Script to find out how many seconds is a secondary replica database is behind the primary replica

Script to find out how many seconds is a secondary replica behind the primary replica 


There can be situations when the secondary replica will go behind the primary replica and you want to know how long will it take for both to get in sync but ensure replicas are not in disconnected state.

This script will tell you the estimated time for sync:-


/* Start of script -- Needs to be run on PRIMARY REPLICA*/

WITH PR(database_id, last_commit_time) AS

(
SELECT dr_state.database_id as database_id,
dr_state.last_commit_time 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
where ar_state.role = 1
)
SELECT ar.replica_server_name AS 'Replica Instance', dr_state.database_id as 'Database ID',
DATEDIFF(s,dr_state.last_commit_time,PR.last_commit_time) 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_id
JOIN PR on PR.database_id = dr_state.database_id

WHERE ar_state.role != 1 and dr_state.synchronization_state = 1

/* End of script */

This script can be used to configure an Alert system where if the synchronization is beyond the RPO limit, an email gets triggered to the DBA mailbox. 

For Example, in below screenshot the secondary replicas [Win2k8r2-2] is 1342 seconds behind its primary replica.

Another Script
---------------------

SELECT AGS.NAME AS AGGroupName

    ,AR.replica_server_name AS InstanceName

    ,HARS.role_desc

    ,Db_name(DRS.database_id) AS DBName

    ,DRS.database_id

    ,is_ag_replica_local = CASE

        WHEN DRS.is_local = 1

            THEN N'LOCAL'

        ELSE 'REMOTE'

        END

    ,AR.availability_mode_desc AS SyncMode

    ,DRS.synchronization_state_desc AS SyncState

    ,DRS.last_hardened_lsn

    ,DRS.end_of_log_lsn

    ,DRS.last_redone_lsn

    ,DRS.last_hardened_time

    ,DRS.last_redone_time

    ,DRS.log_send_queue_size

    ,DRS.redo_queue_size AS 'Redo_Queue_Size(KB)'

    /*

    if the last_hardened_lsn from the primary server == last_hardened_lsn from secondary server

    then there is NO LATENCY

    */

    ,'seconds behind primary' = CASE

            WHEN EXISTS (

                    SELECT DRS.last_hardened_lsn

                    FROM (

                        (

                            sys.availability_groups AS AGS INNER JOIN sys.availability_replicas AS AR ON AGS.group_id = AR.group_id

                            ) INNER JOIN sys.dm_hadr_availability_replica_states AS HARS ON AR.replica_id = HARS.replica_id

                        )

                    INNER JOIN sys.dm_hadr_database_replica_states DRS ON AGS.group_id = DRS.group_id

                        AND DRS.replica_id = HARS.replica_id

                    WHERE HARS.role_desc = 'PRIMARY'

                        AND DRS.last_hardened_lsn = DRS.last_hardened_lsn

                    )

                THEN 0

            ELSE datediff(s, last_hardened_time, getdate())

            end

FROM sys.dm_hadr_database_replica_states DRS

LEFT JOIN sys.availability_replicas AR ON DRS.replica_id = AR.replica_id

LEFT JOIN sys.availability_groups AGS ON AR.group_id = AGS.group_id

LEFT JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id

    AND AR.replica_id = HARS.replica_id

ORDER BY Db_name(DRS.database_id)

    ,is_ag_replica_local