Drive inventory for clustered SQL Server instances using PowerShell

By: Johan Bijnens   |   Updated: 2015-02-19   |   Comments   |   Related: > PowerShell

Problem

If you keep track of your SQL Server instances disk space, you may well be aware that the out-of-the-box extended store procedure xp_fixeddrives doesn't provide enough information as it only reports free space. It lacks total size information and more important, it doesn't report volume mount point information.  Some background information for mount points can be found here.

Another problem is that xp_fixeddrives returns all "visible drives" to the host as physical disks. That may be a problem if you have clustered SQL Server instances, because a clustered instance is supposed to only address the drives that are being exposed to it with a dependency in Windows Cluster manager.

Solution

In this solution I'm using the PowerShell module SQLPS as provided with SQL Server 2014.  As you may or may not know, SQL Server Management Objects ( SMO / SQLSMO ) are exposed when you use SQLPS. That implies that you can consume the SMO object model.

Before we look at the final solution, let's look at some alternatives for getting drive information for your clustered SQL server instances.

Option 1 - Using xp_fixeddrives

Keep in mind, I'm running xp_fixeddrives and I have the necessary privileges. If your account isn't a member of the SQL Server sysadmin group you'll not get an error, but an empty result set. Even if you have been granted CONTROL SERVER you'll get an empty result set!

 exec xp_fixeddrives

In my test case this results to:

Just to avoid any confusion:

• C-drive is the local drive which holds the OS and the software
• R-drive is the drive exposed to the targeted SQL Server instance ( and which also contains 2 mountpoints )
• T-drive is not exposed to the targeted SQL Server instance.
• V-drive is not exposed to the targeted SQL Server instance.

That being said, with regards to my SQL Server instance, I'm only interested in the R-drive.

Option 2 - PowerShell - SQLPS - SMO - EnumAvailableMedia

Have you ever looked at the SQL Server Management Objects Object model? Have a quick peek at the SMO Object Model Diagram.

When browsing the SMO object model at the server level, you'll encounter a method EnumAvailableMedia. The result set it produces actually doesn't hold direct consumable data.  I've had to pull up some BingFu to transform the data into meaningful information. The reference I use nowadays is: get-free-disk-space-from-serverenumavailablemedia-method.

In the next script you'll see I'm just connecting to a SQL Server instance, browsing the model upward using the databases Parent info and calling its EnumAvailableMedia method providing the input parameter 2 for Physical Disk, as I'm only interested in the physical disk information. This script will produce two result sets:

1. the raw data of the EnumAvailableMedia method
2. and the interpreted data.
   

In my test case this results to:

As you can see, once the EnumAvailableMedia results have been interpreted, the results are the same as with xp_fixeddrives.

Option 3 - Using DMV sys.dm_os_volume_stats

Another way of getting to total volume size and available size of disks and mount points used by the instance databases can be found in the Diagnostic Information Queries provided by Glenn Berry. DMV sys.dm_os_volume_stats ( SQL 2008 R2 SP1 ) returns information about the operating system volume (directory) on which the specified database files are stored.

SELECT DISTINCT vs.volume_mount_point,
vs.file_system_type,
vs.logical_volume_name,
CONVERT( decimal( 18 , 2 ) , vs.total_bytes / 1073741824.0 ) AS [Total Size (GB)],
CONVERT( decimal( 18 , 2 ) , vs.available_bytes / 1073741824.0 )AS [Available Size (GB)],
CAST( CAST( vs.available_bytes AS float ) / CAST( vs.total_bytes AS float )AS decimal( 18 , 2 )) * 100 AS [Space Free %]
FROM sys.master_files AS f WITH ( NOLOCK )
CROSS APPLY sys.dm_os_volume_stats( f.database_id , f.[file_id] ) AS vs
OPTION( RECOMPILE );

As you can see, the results report locations that are actually being used by your database, but that may not be all resources that are available to your SQL Server instance through the Windows Cluster definitions.

The big advantage of the DMV is that it also reports the total size for a given volume_mount_point.

For first aid, it is great to have that information available from within the SQL Server engine ( provided your account has been granted at least CONTROL SERVER ).

Option 4 - Using PowerShell remoting to get to the cluster information

The next script requires a Windows credential, i.e. a windows account needs to be provided which has been granted the needed Windows authority to get to the Windows Cluster information.

Further more it holds two functions:

1. Get-DiskSpace ( based on Aaron Nelson's solution )
2. Get-ClusterGroupDiskInfo

Both functions use PowerShell remoting to get directly to the wanted information.

In this demo I used a globally scoped variable $Global:Cred which holds the credential that is used by the functions. I might as well have added a mandatory input parameter, so it would have to be provided with each call or have each function pop up for the credential to be provided, which certainly isn't the preferred way.

As you will see, the final results are produced matching the disk labels with the cluster resource names.
     $AllDisks | Where-Object Label -in $ResourceNames ;

Standardization and naming conventions are to be used to your benefit!

<#
.SYNOPSIS
     list of drives and mount points 
 on a machine available for a given SQLInstance 

.DESCRIPTION
     
 list of drives and mount points on a machine available for a given 
 SQLInstance.
     As we use naming conventions for 
 out LUNs and these are also used on as Label for the mapped drives
     
 we can match them up to get a list of drives which are available for a 
 SQLInstance, due to dependencies. 

.NOTES
     
 -Date 2015-01-13 - Author Bijnens Johan - @alzdba

#>
#Requires 
 -version 3 

#To be provided 

 [string]$Servername='LogicalInstanceServerName' ;
[string]$InstanceName = 
 'InstanceName';

$Global:Cred = Get-Credential 
 "admindomain\admin$env:username" ;


#Region Functions


 Function Get-DisksSpace {
<#
.SYNOPSIS
     
 list of drives and mount points on the machine you listed and default the 
 unit of measure 

.DESCRIPTION
     list of 
 drives and mount points on the machine you listed and default the unit of 
 measure 

.PARAMETER <Parameter-Name>
     If 
 bound parameters, no need to put them overhere


.EXAMPLE
     
 $Unit = 'MB'
     Get-DisksSpace -Servername 
 dv15 -unit $Unit | Select SystemName, Name, 
 @{n="SizeIn$Unit";e={"{0:N2}" -f $_."SizeIn$Unit"}}, 
 @{n="FreeIn$Unit";e={"{0:N2}" -f $_."FreeIn$Unit"}}, 
 @{n='PercentFree';e={"{0:N2}" -f $_.PercentFree}}, Label, DriveType | ft 
 -AutoSize

.NOTES
     -Date 2010-11-24 - 
 Author Aaron Nelson 

.Link
     
 http://sqlvariant.com/wordpress/index.php/2010/11/quick-blog-powershell-disk-and-mountpoint-check/

 
#>

[CmdletBinding()]
param ( [Parameter(Mandatory=$true, 
 Position=0)]
     [string]$Servername=".", 
     
 [Parameter(Mandatory=$false, Position=1)]
     $unit= 
 "GB")

$measure = "1$unit" ;
$remoteSessionConnection = $null ;

 try {
     $remoteSessionConnection = New-PSSession 
 -ComputerName $serverName -Credential $Global:Cred ;

     
 Invoke-Command -Session $remoteSessionConnection -ScriptBlock {
          
 <# Get-CimInstance -ClassName Win32_LogicalDisk does not provice MountPoint 
 information !!! #>
          
 Get-WmiObject Win32_Volume | where DriveType -EQ 3 
          
 } | select SystemName , Name `
                    
 , @{Label="SizeIn$unit";Expression={$_.Capacity/$measure}} `
                    
 , @{Label="FreeIn$unit";Expression={$_.freespace/$measure}} `
                    
 , @{Label="PercentFree";Expression={(($_.freespace / $_.Capacity) * 100)}} `
                    
 , Label, DriveType | Sort Name ;
     }
catch {
     
 $err = $_ ;
     write-verbose $('Error during 
 Get-DisksSpace - {0}' -f $err.Exception.Message) ;
     
 }
finally {
     if ( $remoteSessionConnection ) {
          
 # Close remote session connection 
          
 Remove-PSSession $remoteSessionConnection ;
          
 }
     }

}


Function 
 Get-ClusterGroupDiskInfo {
<#
.SYNOPSIS
     
 list of physical disks ( LUNs ) of a cluster

.DESCRIPTION
     
 list of physical disks ( LUNs ) of a cluster

.PARAMETER 
 <Parameter-Name>
     If bound parameters, no need to 
 put them overhere


.EXAMPLE
     
 Get-ClusterGroupDiskInfo -Servername yourClusterNode -ClusterGroupNameLike 
 '*yourInstanceName*' 

.NOTES
     -Date 
 2010-11-24 - Author Johan Bijnens 

#>

[CmdletBinding()]

 param ( [Parameter(Mandatory=$true, Position=0)]
          
 [string]$Servername, 
          
 [Parameter(Mandatory=$false, Position=1)]
          
 $ClusterGroupNameLike= "*")

$remoteSessionConnection = $null ;
try 
 {
     $remoteSessionConnection = New-PSSession 
 -ComputerName $serverName -Credential $Global:Cred ;

     
 $RmtInfo = Invoke-Command -Session $remoteSessionConnection -ScriptBlock {
          
 Import-Module FailoverClusters ;
          
 # read cluster resources
          
 Get-ClusterResource ;
          
 } 

     Remove-PSSession $remoteSessionConnection 
 ;

     $RmtGroupInfo = $RmtInfo | where 
 OwnerGroup -like $ClusterGroupNameLike | sort ownergroup, resourcetype, name 
 ; 

     $RmtGroupInfo | Where-Object ResourceType 
 -eq 'Physical Disk' | Select -ExpandProperty Name ;

     
 }
catch {
     $err = $_ ;
     
 write-verbose $('Error during operation - {0}' -f $err.Exception.Message) ;
     
 }
finally {
     if ( $remoteSessionConnection ) {
          
 # Close remote session connection 
          
 Remove-PSSession $remoteSessionConnection ;
          
 }
     }

}

#EndRegion Functions


 ## Start of actual script

$ClusterGroupNameLike = "*$InstanceName*";

 
$ResourceNames = Get-ClusterGroupDiskInfo -Servername $Servername 
 -ClusterGroupNameLike $ClusterGroupNameLike ; 
write-verbose $('count of 
 ResourceNames {0}' -f $ResourceNames.count );

$SizeUnit = 'MB' ;

 $AllDisks = Get-DisksSpace -Servername $serverName -unit $SizeUnit ;

 write-verbose $('count of AllDisks {0}' -f $AllDisks.count );


 $SQLInstanceVisibleDisks = $AllDisks | Where-Object Label -in $ResourceNames 
 ;

write-verbose $('count of SQLInstanceVisibleDisks {0}' -f 
 $SQLInstanceVisibleDisks.count );

$SQLInstanceVisibleDisks | Select 
 @{n='InstanceName';e={$InstanceName}}, @{n='Location';e={$_.Name}}, 
 @{n="SizeIn$SizeUnit";e={"{0:N2}" -f $_."SizeIn$SizeUnit"}}, 
 @{n="FreeIn$SizeUnit";e={"{0:N2}" -f $_."FreeIn$SizeUnit"}}, 
 @{n='PercentFree';e={"{0:N2}" -f $_.PercentFree}}, Label, DriveType | ft 
 -AutoSize ;


# I don't like credentials just hanging around

 $Global:Cred = $null ;


Write-Host 'The end';

In my test case this results to:

You'll have to evaluate whether the missing system drive information outweighs the redundant information stored ( including locations that are not to be used by your instance ) in your inventory system versus the fact that an information consumer always has to figure out which actual locations are available to your instance.

Conclusion

As shown, this final result exposes some really interesting information with regards to the SQL Server instances space consumption and space availability, because it also shows total size of any physical disk ( or LUN ) - that is provided for the instance through Windows Clustering - and its free space.  Another reason to put in place some naming conventions!

I hope you've enjoyed this little quest for data and I'm looking forward to read your alternatives, optimizations and comments.

Next Steps

• Test the script, inspect the results
• Explore the SQL Server Management Objects library
• Check out the MSSQLTips.com PowerShell Tips
• Enjoy the marvels of PowerShell and SQLPs

About the author

Johan Bijnens is a database firefighter, working with SQL Server since 2000, working at Aperam Genk.

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

View all my tips