AG-Resume.sql

--AG-Resume.sql for SQL Agent job

--Check Sync State and Execute Resume
DROP TABLE IF EXISTS #agAynamicSqlResume

DECLARE @sqlCommand NVARCHAR(max)

SELECT ';ALTER DATABASE [' + Db_name(DRS.database_id) + '] SET HADR RESUME' AS [resume_sql],
       AGS.name AS AGGroupName,
       AGL.dns_name AS Listener_dns_name,
       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,
       AR.failover_mode_desc,
       DATEDIFF(MINUTE, DRS.last_redone_time, DRS.last_hardened_time) AS DELAY_min,
       --DATEDIFF(SECOND, DRS.last_redone_time, DRS.last_hardened_time) AS DELAY_sec,
       --DATEDIFF(MILLISECOND, DRS.last_redone_time, DRS.last_hardened_time) AS DELAY_ms,
       DRS.redo_queue_size,
       DRS.log_send_queue_size,
       DRS.last_hardened_time,
       DRS.last_redone_time,
       DRS.last_hardened_lsn,
       AGL.port AS Listener_port,
       AGL.ip_configuration_string_from_cluster AS Cluster_IP_addresses,
       DRS.end_of_log_lsn,
       DRS.last_redone_lsn
INTO #agAynamicSqlResume
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
    LEFT JOIN sys.availability_group_listeners AGL
        on AGL.group_id = AGS.group_id
--WHERE HARS.role_desc = 'PRIMARY'
--WHERE Db_name(DRS.database_id)= 'SomeDbName' 
--WHERE DRS.synchronization_state_desc = 'SYNCHRONIZED'

WHERE DRS.synchronization_state_desc = 'NOT SYNCHRONIZING'
--OR DRS.synchronization_state_desc = 'NOT SYNCHRONIZING'
WHILE EXISTS (SELECT TOP (1) resume_sql FROM #agAynamicSqlResume)
BEGIN
    SET @sqlCommand = ( SELECT TOP (1) resume_sql FROM #agAynamicSqlResume )
    --SELECT @sqlCommand;
    PRINT @sqlCommand;
    EXECUTE sp_executesql @sqlCommand;
    --AG Resume typically needs to be run twice
    EXECUTE sp_executesql @sqlCommand;

    DELETE TOP (1) FROM #agAynamicSqlResume WHERE resume_sql = @sqlCommand
END
--https://stackoverflow.com/questions/11991987/exec-failed-because-the-name-not-a-valid-identifier/11992108#11992108