Identifying Windows Volumes on a Clustered SQL Server Instance with the DMVs

By:   |   Comments (1)   |   Related: > Clustering


I have inherited a Microsoft SQL clustered server and I don't know much about it's logical structure. It is a two node, active-active cluster and I am trying to determine which of the logical volumes on the shared disk are associated with each SQL Server instance. Is there a simple method in T-SQL to determine this without having to fire up Cluster Administrator?


There most certainly is. One of the Dynamic Management Views (DMV) that released with SQL Server 2005 is sys.dm_io_cluster_shared_drives. Let's briefly discuss what DMVs are first for those that may be new to the concept before I answer your question.

Dynamic Management Views provide insight into internal Microsoft SQL Server metrics. They function just as database views do: they act as a logical object that is really simply an aggregate of data from joined tables and views, usually allowing for a simplified method of querying disparate data sets. In the case of DMVs these tables and views are internal to the database engine, are system objects, and are even in some cases hidden from the end user. Dynamic Management Views act as a window into the inner workings of the SQL Server instance. The process by which you query the DMVs is identical to how you would query any other view:

SELECT [field1][field2][fieldN] 
WHERE [fieldX] value 
ORDER BY [field1][field2]

Now to address your question. sys.dm_io_cluster_shared_drives will return a listing of all logical drive volume names for every shared drive of the current server instance. If the server instance is not clustered, no results are returned. The structure of this DMV is extremely simple, just a single column, DriveName.  I too have a two node, active-active cluster. This is a misnomer however.  The new terminology would be multi-node, multi-instance cluster as you may have one or more instances on a give node (server) in a SQL Server cluster.

Below is the query code.  Note that there are absolutely no parameters.  The hardest part of constructing this query is remembering the name of the Dynamic Management View. 

SELECT DriveName 
FROM sys.dm_io_cluster_shared_drives 
ORDER BY DriveName

If I run this query against both nodes I get the following results in my environment:

Node1 Results


Node2 Results


These results in-hand I can ascertain that Node 1's instance is associated with the L, M, N, and O logical disk volumes. Node 2's instance is reliant upon the R, S, T, and U logical disk volumes. All this information was retrieved with the simplest of T-SQL queries, without the need to launch Cluster Administrator.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, January 14, 2009 - 11:29:43 AM - ThomasLL Back To Top (2562)

How do you get the Mounted Volume list?



get free sql tips
agree to terms