Drive inventory for clustered SQL Server instances using PowerShell


By:   |   Updated: 2015-02-19   |   Comments   |   Related: More > 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:

xp_fixeddrives result

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.

#To be provided !
[string]$Servername = 'YourLogicalSQLServerName';
[string]$InstanceName = 'YourInstanceName';


# Check module SQLPS
if ( !(get-module -name SQLPs ) ) {

    # save original location
    Push-Location
    # SQLPs will set the current location to SQLSERVER:\ !!
    # -DisableNameChecking -> avoid remarks about non-discoverable function names
    import-module -name SQLPs -DisableNameChecking | out-null

    #reset current location to original location
    Pop-Location

    }

#Interrupt when errors occur
Trap {
    # Handle the error
    $err = $_.Exception
    write-host $err.Message
    while( $err.InnerException ) {
        $err = $err.InnerException
        write-host $err.Message
        };
    # End the script.
    break
    }

# Using Windows authenticated connection

$Db = Get-SqlDatabase -ServerInstance "$Servername\$InstanceName" -Name 'tempdb'

# physical disks = media type 2
$db.parent.EnumAvailableMedia(2) | Format-Table -AutoSize ;

# https://social.msdn.microsoft.com/Forums/en-US/02adc35b-0fe0-4800-a635-d4ee7b3fa873/get-free-disk-space-from-serverenumavailablemedia-method-?forum=sqlsmoanddmo
$Db.parent.EnumAvailableMedia(2) | Select Name, @{n='FreeMB';e={"{0:N2}" -f (([Convert]::ToInt64([Math]::Abs($_.LowFree)) + [Convert]::ToInt64([Math]::Abs($_.HighFree) * [Math]::Pow(2,32) ) ) / 1MB) }} | ft -AutoSize ;

#Disconnect from the instance
$db.Parent.ConnectionContext.Disconnect();

In my test case this results to:

EnumAvailableMedia result

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 );

DMV results

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:

Remoting to cluster result

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


Last Updated: 2015-02-19


get scripts

next tip button



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

View all my tips
Related Resources





Comments For This Article





download





Recommended Reading

Setting the PowerShell Execution Policy

How to find a specific text string in a SQL Server Stored Procedure, Function, View or Trigger

Using PowerShell to Work with Directories and Files

Bulk Copy Data from Oracle to SQL Server

Execute SQL Server Stored Procedures from PowerShell








get free sql tips
agree to terms


Learn more about SQL Server tools