How to Identify SQL Server Cluster Node and Shared Drives Information Using TSQL Queries
July 16, 2010
When you have a Multiple SQL Server Cluster Configuration and multiple shared drives it becomes tough to know which SQL Server Failover Cluster Instance is using which Shared Drives. This article has the collection of TSQL queries which can help you identify Cluster Node and Shared Drive Information when you have Multiple SQL Server Failover Cluster Configurations.
Identify is your SQL Server Clustered using TSQL Query
The below t-sql query will help you identify where the SQL Server Instance you are supporting is clustered on non cluster instance.
The above query will return either of the below mentioned options.
If clustered the above query will return the value as “1”
If it’s a not clustered instance then the above query will return the value as “0”
Identify SQL Server Machine Name, Instance Name and Server Name using TSQL Query
Execute the below query to identify SQL Server Machine Name, Instance Name and Server Name
TechNet explanation for each of the above property is:-
- MachineName: - Windows computer name on which the server instance is running. For a clustered instance, an instance of SQL Server running on a virtual server on Microsoft Cluster Service, it returns the name of the virtual server.
- InstanceName: - Name of the instance to which the user is connected.
- ServerName: - Both the Windows server and instance information associated with a specified instance of SQL Server.
Identify the Node on which SQL Server Instance is Currently Running using TSQL Query
Execute the below query to identify the node on which SQL Server instance is currently running.
TechNet explanation for the above property is: -
- ComputerNamePhysicalNetBIOS: - NetBIOS name of the local computer on which the instance of SQL Server is currently running. For a clustered instance of SQL Server on a failover cluster, this value changes as the instance of SQL Server fails over to other nodes in the failover cluster. On a stand-alone instance of SQL Server, this value remains constant and returns the same value as the MachineName property.
Recommendation: - MyTechMantra.com recommends the use of performance monitoring tools to be run on the SQL Server to identify potential bottlenecks. Hence we recommend you to take a look at "Database Performance Analyzer" one such Free Tool to avoid issues proactively.
Identify Nodes of SQL Server Failover Cluster using TSQL Query
You can identify the nodes of SQL Server Failover cluster either by using an inbuilt function or using a DMV. Below mentioned queries will return the same result.
/* Using In-Built Function Identify Nodes of SQL Server
Failover Cluster */
SELECT * FROM fn_virtualservernodes()
/* Using DMV Identify Nodes of SQL Server Failover Cluster */
SELECT * FROM sys.dm_os_cluster_nodes
Identify all drives on the node and free space using TSQL queries
Now that you know the SQL Server Nodes the next thing which you would like to know will be the different drives which are available and the free space in each drive. This information you can quickly reterive using XP_FIXEDDRIVES undocumented extended stored procedure.
In the above snippet you could see that there are 9 drives available on the node. However, at this point you wont know which are the drives used by SQL Server Cluster.
Identify Shared Drives used by SQL Server Cluster using TSQL queries
You can identify the shared drives used by SQL Server Failover Cluster Instance either by using an inbuilt function or using a DMV. Below mentioned queries will return the same result.
/* Using In-Built Function Identify Shared Drives Used by SQL
Server Failover Cluster Instance */
SELECT * FROM fn_servershareddrives()
/* Using DMV Function Identify Shared Drives Used by SQL Server Failover Cluster Instance */
SELECT * FROM sys.dm_io_cluster_shared_drives
In the above snippet you could see that “F”, “J”, “I” an “H” is shared drives which belong to currently Instance of SQL Server Cluster.
All the above mentioned queries are very useful to a database administrator who has to manage a multiple failover cluster configurations in a very large SQL Server Environment.
Read more about
SERVERPROPERTY on TechNet
Read more about fn_virtualservernodes() on TechNet
Read more about sys.dm_os_cluster_nodes on TechNet
Read more about fn_servershareddrives on TechNet
Read more about sys.dm_io_cluster_shared_drives on TechNet
Geeks who read this article also read…
- Performance Dashboard Reports in SQL Server 2012
- How to Backup an Analysis Services Database Using SQL Server Management Studio
- How to Restore an Analysis Services Database Using SQL Server Management Studio
- Automate Backup of Analysis Services Database Using SQL Server Agent Job
- How to Attach Analysis Services Database in SQL Server
- How to Detach Analysis Services Database in SQL Server
- How to Backup Database in SQL Server
- How to Restore Database in SQL Server
- How to Attach Database Without a Transaction Log File in SQL Server
- New Features in SQL Server 2012 Reporting Services for Developers
- New Features in SQL Server 2012 for Database Administrators
- New Features in SQL Server 2012 for Database Developers
- Read More SQL Server Articles…