How to see the errors on AlwaysOn using T-SQL?

Question

when I run the following query

if you run the query below on bocss it says it is not healthy

select * from 
sys.dm_hadr_availability_replica_states 

it tells me my alwayson is not healthy.

speaking with another DBAs in my team I came to know that this is so because we have removed the alwayson AG but we left it so we can add it back easily

So right clicking on it, I get to the errors\warnings shown on the pictures below:

I check with the scripts below:

--  Always On Status Report
--
-- This script will show the status of the Alway On replication status

SELECT DISTINCT
primary_replica as 'Primary Server',
[endpoint_url] as 'End Point URL',
primary_recovery_health_desc as 'Primary Server Health Status',
secondary_recovery_health_desc as 'Secondary Server Health Status',
operational_state_desc as 'Operational State',
connected_state_desc as 'Connection State',
recovery_health_desc as 'Recovery Health',
synchronization_state_desc as 'Synchronization State',
database_state_desc as 'Database State',
JOIN_state_desc as 'Join State',
suspend_reason_desc as 'Suspended Reason',
availability_mode_desc as 'Availability Mode',
failover_mode_desc as 'Failover Mode',
primary_role_allow_connections_desc as 'Primary Connections Allowed',
secondary_role_allow_connections_desc as 'Secondary Connections Allowed',
create_date as 'Date Created',
modify_date as 'Date Modified',
[backup_priority] as 'Backup Priority',
role_desc as 'Role Type',
last_connect_error_description as 'Last Connection Error',
last_connect_error_timestamp as 'Last Connection Error Time',
last_sent_time as 'Last Data Send Time',
last_received_time as 'Last Data Recieved TIme',
last_hardened_time  as 'Last Hardened Time',
last_redone_time as 'Last Redone Time',
log_send_queue_size as 'Log Send Queue Size',
log_send_rate as 'Log Send Rate',
redo_queue_size as 'Redo Queue Size',
redo_rate as 'Rate of Redo',
filestream_send_rate as 'Filestream Send Rate',
last_commit_time as ' Last Commit Time',
low_water_mark_for_ghosts as 'Low Water Mark for Ghosts'
FROM sys.dm_hadr_availability_group_states

LEFT OUTER JOIN  sys.availability_replicas
ON sys.dm_hadr_availability_group_states.group_id =  sys.availability_replicas.group_id

LEFT OUTER JOIN sys.dm_hadr_availability_replica_cluster_states
ON sys.dm_hadr_availability_group_states.group_id =  sys.dm_hadr_availability_replica_cluster_states.group_id

LEFT OUTER JOIN sys.dm_hadr_availability_replica_states
ON sys.dm_hadr_availability_group_states.group_id =  sys.dm_hadr_availability_replica_states.group_id

LEFT OUTER JOIN sys.dm_hadr_database_replica_states
ON sys.dm_hadr_availability_group_states.group_id =  sys.dm_hadr_database_replica_states.group_id

--WHERE operational_state_desc IS NOT NULL
--AND database_state_desc IS NOT NULL
ORDER BY [endpoint_url] DESC

and also this script:

-- alwayson troubleshooting
-- marcelo miorelli
-- 15-feb-2017


  select * into #tmpag_availability_groups 
  from master.sys.availability_groups

  select agstates.group_id, agstates.primary_replica into #tmpag_availability_group_states 
  from master.sys.dm_hadr_availability_group_states as agstates


  select group_id, replica_id, replica_metadata_id, replica_server_name,create_date, modify_date, endpoint_url, read_only_routing_url, primary_role_allow_connections, secondary_role_allow_connections, availability_mode,failover_mode, session_timeout, backup_priority, owner_sid 
  into #tmpag_availability_replicas 
  from master.sys.availability_replicas



    select group_id, replica_id, is_local, role,operational_state,recovery_health,synchronization_health,connected_state, last_connect_error_number,last_connect_error_description, last_connect_error_timestamp 
    into #tmpar_availability_replica_states 
    from master.sys.dm_hadr_availability_replica_states



      select replica_id,join_state 
      into #tmpar_availability_replica_cluster_states 
      from master.sys.dm_hadr_availability_replica_cluster_states



SELECT
CAST(
        serverproperty(N'Servername')
       AS sysname) AS [Server_Name],
AG.name AS [Name],
ISNULL(AG.automated_backup_preference, 4) AS [AutomatedBackupPreference],
ISNULL(AG.failure_condition_level, 6) AS [FailureConditionLevel],
ISNULL(AG.health_check_timeout, -1) AS [HealthCheckTimeout],
AR2.replica_metadata_id AS [ID],
ISNULL(arstates2.role, 3) AS [LocalReplicaRole],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
AG.group_id AS [UniqueId]
FROM
#tmpag_availability_groups AS AG
LEFT OUTER JOIN #tmpag_availability_group_states as agstates ON AG.group_id = agstates.group_id
INNER JOIN #tmpag_availability_replicas AS AR2 ON AG.group_id = AR2.group_id
INNER JOIN #tmpar_availability_replica_states  AS arstates2 ON AR2.replica_id = arstates2.replica_id AND arstates2.is_local = 1
ORDER BY
[Name] ASC



    --------------------



        SELECT
            arrc.replica_server_name, 
            COUNT(cm.member_name) AS node_count,
            SUM(cm.member_state) AS member_state_sum,
            SUM(cm.number_of_quorum_votes) AS quorum_vote_sum
        INTO #tmpar_availability_replica_cluster_info
        FROM 
            (SELECT DISTINCT replica_server_name, node_name FROM master.sys.dm_hadr_availability_replica_cluster_nodes) AS arrc 
        LEFT OUTER JOIN master.sys.dm_hadr_cluster_members AS cm ON UPPER(arrc.node_name) = UPPER(cm.member_name) 
        GROUP BY arrc.replica_server_name



      select * into #tmpar_ags from master.sys.dm_hadr_availability_group_states
      select ar.group_id, ar.replica_id, ar.replica_server_name, ar.availability_mode, 
      (case when UPPER(ags.primary_replica) = UPPER(ar.replica_server_name) then 1 else 0 end) as role, 
      ars.synchronization_health 
      into #tmpar_availabilty_mode 
      from #tmpag_availability_replicas as ar
      left join #tmpar_ags as ags on ags.group_id = ar.group_id
      left join #tmpar_availability_replica_states as ars on ar.group_id = ars.group_id and ar.replica_id = ars.replica_id
      select am1.replica_id, am1.role, (case when (am1.synchronization_health is null) then 3 else am1.synchronization_health end) as sync_state, (case when (am1.availability_mode is NULL) or (am3.availability_mode is NULL) then null when (am1.role = 1) then 1 when (am1.availability_mode = 0 or am3.availability_mode = 0) then 0 else 1 end) as effective_availability_mode
      into #tmpar_replica_rollupstate from #tmpar_availabilty_mode as am1 left join (select group_id, role, availability_mode from #tmpar_availabilty_mode as am2 where am2.role = 1) as am3 on am1.group_id = am3.group_id





SELECT
CAST(
        serverproperty(N'Servername')
       AS sysname) AS [Server_Name],
AG.name AS [AvailabilityGroup_Name],
AR.replica_server_name AS [Name],
ISNULL(AR.availability_mode, 2) AS [AvailabilityMode],
ISNULL(AR.backup_priority, -1) AS [BackupPriority],
ISNULL(AR.primary_role_allow_connections, 4) AS [ConnectionModeInPrimaryRole],
ISNULL(AR.secondary_role_allow_connections, 3) AS [ConnectionModeInSecondaryRole],
ISNULL(arstates.connected_state, 2) AS [ConnectionState],
ISNULL(AR.create_date, 0) AS [CreateDate],
ISNULL(AR.modify_date, 0) AS [DateLastModified],
ISNULL(AR.endpoint_url, N'') AS [EndpointUrl],
ISNULL(AR.failover_mode, 2) AS [FailoverMode],
ISNULL(arcs.join_state, 99) AS [JoinState],
ISNULL(arstates.last_connect_error_description, N'') AS [LastConnectErrorDescription],
ISNULL(arstates.last_connect_error_number,-1) AS [LastConnectErrorNumber],
ISNULL(arstates.last_connect_error_timestamp, 0) AS [LastConnectErrorTimestamp],

CASE 
    WHEN arci.member_state_sum IS NULL OR arci.node_count = 0 THEN 3
    WHEN arci.member_state_sum = 0 THEN 0 
    WHEN arci.member_state_sum < arci.node_count THEN 2
    WHEN arci.member_state_sum = arci.node_count THEN 1
    ELSE 3
END
       AS [MemberState],
ISNULL(arstates.operational_state, 6) AS [OperationalState],
suser_sname(AR.owner_sid) AS [Owner],
ISNULL(arci.quorum_vote_sum, -1) AS [QuorumVoteCount],
ISNULL(AR.read_only_routing_url, '') AS [ReadonlyRoutingConnectionUrl],
ISNULL(arstates.role, 3) AS [Role],
ISNULL(arstates.recovery_health, 2) AS [RollupRecoveryState],
(case when arrollupstates.sync_state = 3 then 3 when (arrollupstates.effective_availability_mode = 1 or arrollupstates.role = 1) then arrollupstates.sync_state when arrollupstates.sync_state = 2 then 1 else 0 end) AS [RollupSynchronizationState],
ISNULL(AR.session_timeout, -1) AS [SessionTimeout],
AR.replica_id AS [UniqueId]
FROM
#tmpag_availability_groups AS AG
INNER JOIN #tmpag_availability_replicas  AS AR ON (AR.replica_server_name IS NOT NULL) AND (AR.group_id=AG.group_id)
LEFT OUTER JOIN #tmpar_availability_replica_states AS arstates ON AR.replica_id = arstates.replica_id
LEFT OUTER JOIN #tmpar_availability_replica_cluster_states AS arcs ON AR.replica_id = arcs.replica_id
LEFT OUTER JOIN #tmpar_availability_replica_cluster_info AS arci ON UPPER(AR.replica_server_name) = UPPER(arci.replica_server_name) 
LEFT OUTER JOIN #tmpar_replica_rollupstate AS arrollupstates ON AR.replica_id = arrollupstates.replica_id
ORDER BY
[AvailabilityGroup_Name] ASC,[Name] ASC




      drop table #tmpar_availabilty_mode
      drop table #tmpar_ags
      drop table #tmpag_availability_groups
      drop table #tmpag_availability_group_states
      drop table #tmpag_availability_replicas
      drop table #tmpar_availability_replica_states 
      DROP TABLE #tmpar_availability_replica_cluster_states
      DROP TABLE #tmpar_availability_replica_cluster_info
      DROP TABLE #tmpar_replica_rollupstate




-------------------------------------

but I cannot get hold of the error messages described on the pictures shown above.

is there a T-SQL way to get to the errors/warnings related to the alwayson so that I can find out what is wrong?

Answer 1

but I cannot get hold of the error messages described on the pictures shown above.

Those error messages, much as @Nic pointed out, are not really error messages in the log or in a DMV but are surfaced up through policy based management which runs as part of the AlwaysOn Dashboard.

If you wanted to see how this works, we can open up one of the PBM system policies for Availability Groups.

is there a T-SQL way to get to the errors/warnings related to the alwayson so that I can find out what is wrong?

Nothing that is going to give you the "messages" such as the PBM policies does. Looking at the DMVs as you have above are going to give you the best information for what's currently happening now.

In the example you have, looking at sys.dm_hadr_database_replica_states will have a synchronization_state_desc column that will tell you what's going on. There are additional DMVs which the script you already posted does look at to help identify what's going on.

If you wanted this logged, there is nothing stopping you from creating your own policies and having them run on a schedule - then using an agent alert to give you a feedback mechanism for the PBM policy failures... it will just tell you something happened, though, and won't give you pretty text.

The best would be a roll your own monitoring with common problem finding or looking at other monitoring solutions which may give you nice pretty formatted text in the alerts.