TSQL Script to check Always On Health Status

This week, today, the post will cover an interesting script. I believe there is already a method that gives us Always On Health status details using UI/Report. Sometimes back, when I was talking to a friend of mine he was asking me if I have a TSQL Script to check Always On Health Status. I recall that I have a script handy which I wrote sometimes back in 2016 which I have already shared on the TechNet Gallery, but let me share it here as well.

TSQL Script to check Always On Health Status

-- Script by: Hemantgiri S. Goswami 
-- Blog: https://www.sqlservercitation.com/  
-- Twitter: @Ghemant
-- Version 1.0
-- Modification : 26th May 2016
-- AG Health Status

DECLARE @HADRName	varchar(25)
select n.group_name,n.replica_server_name,n.node_name,rs.role_desc,
db_name(drs.database_id) as 'DBName',drs.synchronization_state_desc,drs.synchronization_health_desc
from sys.dm_hadr_availability_replica_cluster_nodes n
join sys.dm_hadr_availability_replica_cluster_states cs
on n.replica_server_name = cs.replica_server_name
join sys.dm_hadr_availability_replica_states rs 
on rs.replica_id = cs.replica_id
join sys.dm_hadr_database_replica_states drs
on rs.replica_id=drs.replica_id
where n.replica_server_name <> @HADRName
on rs.replica_id=drs.replica_id

The output will look like below:

I hope you all will like the above script. And yes, I have one more thing for you guys to help you. Just wanted to let you all know, in case you have already decided that you will be traveling to Seattle to attend SQLPASS Summit 2017 and haven’t completed your registration already, please use the below discount code while registering. It will avail you 150 US$ discount

Use Chapter Name: Surat User Group
Unique Discount Code: LC15KCF8

*The discount code cannot be combined with any other PASS Summit 2016 registration, and cannot be applied retroactively

Leave a Reply

Your email address will not be published. Required fields are marked *

Hemantgiri Goswami

Hemantgiri Goswami

Hemantgiri is a seasoned SQL Server Consultant with demonstrated history for close to 21 years. He is a published author specializing in High Availability and Disaster Recovery area. He was awarded Most Valuable Professional by Microsoft 4 times. He is a regular speaker at events in Surat. Hemantgiri is founder and leader of the SQLPASS Chapter for Surat.