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) 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