Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- WITH AG_Stats AS (
- SELECT AGS.name AS AGGroupName,
- AR.replica_server_name AS InstanceName,
- HARS.role_desc,
- Db_name(DRS.database_id) AS DBName,
- DRS.database_id,
- 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, -- On a secondary database, time of the log-block identifier for the last hardened LSN (last_hardened_lsn).
- DRS.last_redone_time, -- Time when the last log record was redone on the secondary database.
- DRS.log_send_queue_size,
- DRS.redo_queue_size,
- DRS.last_commit_time
- 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
- --where database_id=9
- ),
- Pri_CommitTime AS
- (
- SELECT DBName
- , last_commit_time
- FROM AG_Stats
- WHERE role_desc = 'PRIMARY'
- ),
- FO_CommitTime AS
- (
- SELECT DBName, last_commit_time
- FROM AG_Stats
- WHERE role_desc = 'SECONDARY'
- )
- SELECT p.[DBName] AS [DatabaseName], p.last_commit_time AS [Primary Commit]
- , f.last_commit_time AS [FailOver Commit]
- , DATEDIFF(ss,f.last_commit_time,p.last_commit_time)/(60*60*24) [Days]
- , (DATEDIFF(ss,f.last_commit_time,p.last_commit_time)/(60*60))%24 [Hours]
- , (DATEDIFF(ss,f.last_commit_time,p.last_commit_time)/60)%60 [Minutes]
- , DATEDIFF(ss,f.last_commit_time,p.last_commit_time) AS [Lag (secs)]
- FROM Pri_CommitTime p
- LEFT JOIN FO_CommitTime f ON [f].[DBName] = [p].[DBName]
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement