Close this search box.

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.

					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

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

Leave a Reply

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

Picture of Hemantgiri Goswami

Hemantgiri Goswami

Throughout my extensive 24-year tenure in the IT industry, I have honed my expertise in SQL Server and cloud technologies. My qualifications include certifications in ITIL, Azure, and Google Cloud, and my professional journey boasts a consistent record of delivering top-notch, dependable, and efficient solutions across diverse clients and domains. In recognition of my dedication and impact, I am honored to have received the Microsoft MVP award for SQL Server on six occasions. Additionally, I actively contribute to various online forums and blogs, acting as a moderator and facilitator of meaningful discussions. My ultimate mission revolves around empowering organizations to enhance the reliability and efficiency of their SQL Server implementations while fostering a culture of continuous learning and growth within the SQL Server community.