sys.dm_os_cluster_nodes (Transact-SQL)

 

Updated: August 9, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Returns one row for each node in the failover cluster instance configuration. If the current instance is a failover clustered instance, it returns a list of nodes on which this failover cluster instance (formerly "virtual server") has been defined. If the current server instance is not a failover clustered instance, it returns an empty rowset.

NOTE: To call this from Azure SQL Data Warehouse or Parallel Data Warehouse, use the name sys.dm_pdw_nodes_os_cluster_nodes.

Column nameData typeDescription
NodeNamesysnameName of a node in the SQL Server failover cluster instance (virtual server) configuration.
statusintStatus of the node in a SQL Server failover cluster instance: 0, 1, 2, 3, -1. For more information, see GetClusterNodeState Function.
status_descriptionnvarchar(20)Description of the status of the SQL Server failover cluster node.

0 = up

1 = down

2 = paused

3 = joining

-1 = unknown
is_current_ownerbit1 means this node is the current owner of the SQL Server failover cluster resource.
pdw_node_idintApplies to: Azure SQL Data Warehouse, Parallel Data Warehouse

The identifier for the node that this distribution is on.

When failover clustering is enabled, the SQL Server instance can run on any of the nodes of the failover cluster that are designated as part of the SQL Server failover cluster instance (virtual server) configuration.

NOTE: This view replaces the fn_virtualservernodes function, which will be deprecated in a future release.

Requires VIEW SERVER STATE permission on the instance of SQL Server.

The following example uses sys. dm_os_cluster_nodes to return the nodes on a clustered server instance.

SELECT NodeName, status, status_description, is_current_owner   
FROM sys.dm_os_cluster_nodes;  

Here is the result set.

NodeNamestatusstatus_descriptionis_current_owner
node10up1
node20up0
Node31down0

sys.dm_os_cluster_properties (Transact-SQL)
sys.dm_io_cluster_shared_drives (Transact-SQL)
sys.fn_virtualservernodes (Transact-SQL)
Dynamic Management Views and Functions (Transact-SQL)

Community Additions

ADD
Show: