sys.dm_os_cluster_nodes – Day 12 – One DMV a Day

Hello Geeks and welcome to the Day 12 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.

Since yesterday we have started talking about OS related DMVs. Today I will talk about one more useful DMV for DBAs. In most cases the DBA is not given RDP access to machine on which SQL Server is installed. Especially production servers. So there is always few questions which you want to answer like what are the nodes in my cluster. Sys.dm_os_cluster_nodes is your answer.

Due to some security compliances few companies follow a DBA may not have remote access to the windows servers. In those cases it is hard for DBA to troubleshoot or even answer few simple questions. What are the nodes, is my SQL server on the right node, where can it failover?

Sys.dm_os_cluster_nodes has been introduced in SQL Server 2005 when the DMVs were introduced. But till 2008 R2 it only provided the node names and no additional information. In 2012 they have introduced the below additional columns which are very useful in the above scenario. In case of stand-alone machines the DMV does not return any results

Sys.dm_os_cluser_nodes exposes this information.

NodeName – Nodes in the cluster
Status and Status_description
    0 -UP
    1 -Down
    2 -Paused
    3 -Joining
    -1 –Unknown
Is_current_owner – 1 if this is the owner of the current instance. Else 0



SELECT * FROM sys.dm_os_cluster_nodes


Tomorrow I will talk about another OS related DMV. So, stay tuned. Till then

Happy Learning,

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook


About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

Leave a Reply

Your email address will not be published.