TSQL Script to check Always On Health Status

Helping businesses improve reliability and efficiency of SQL Server

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)
SET @HADRName = @@SERVERNAME
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.

If you encounter such issues please refer to this article Resolving High CPU usage in SQL Server.