SQL Server 2014 Health check DMV queries Day 6

Hi guys in the Previous post i explained How we can Getting the Server Model Number ,Getting the Processor Description and get SQL Server Error Log Properties put today in DAY 6 i will show new DMV in our track Instance Level configuration .

in this post i will explain three new DMV related to SQL Server Cluster and AlwaysOn AG You will see no results if your instance is not using Clustering or AlwaysOn AG.

DMV#14 Get information about your OS cluster

If your SQL server is cluster not standalone you can use this DMV to get very useful information about the OS (Operation System)

SELECT VerboseLogging, SqlDumperDumpFlags, SqlDumperDumpPath,

 SqlDumperDumpTimeOut, FailureConditionLevel, HealthCheckTimeout

FROM sys.dm_os_cluster_properties WITH (NOLOCK) OPTION (RECOMPILE);

DMV#15 Cluster Node Properties

by this DMV we can return the node name and the current status of it so know you can now easily SQL Server cluster running in which node .

SELECT NodeName, status_description, is_current_owner

FROM sys.dm_os_cluster_nodes WITH (NOLOCK) OPTION (RECOMPILE);

DMV#16 Failover Cluster Node with AlwaysOn AG

by this DMV you can return the Cluster name , quorum_type_desc and quorum_state_desc for SQL Server AlwaysOn

SELECT cluster_name, quorum_type_desc, quorum_state_desc

FROM sys.dm_hadr_cluster WITH (NOLOCK) OPTION (RECOMPILE);

To check all my posts you can direct to my personal website or you can download this Excel sheet My Technical POSTS

View all my tips , LinkedIn , Slideshare ,Youtube Channel.

Published by Mustafa EL-Masry

I am Microsoft database consultant working as a Database administrator for more than +10 Years I have very good knowledge about Database Migration, Consolidation, Performance Tuning, Automation Using T-SQL, and PowerShell and so many other tasks I do it in multiple customers here in KSA and as of now, I am working in Bank Albilad managing the core banking system that is hosted in SQL Server Database 8 TB. Also, I am Microsoft certified 2008 and 2016 in SQL Server (2x MCTS, 2x MCTIP, MCSA, MCSE) and I am Microsoft Certified Trainer (MCT) also I am azure Certified (AZ-900, AZ-103) also I was awarded by Microsoft Azure Heroes 3 times as (Azure Content hero, Azure Community hero and Azure Mentor) For more information check my page https://mostafaelmasry.com/about-me/ View all posts by Mustafa EL-Masry