Using Powershell to obtain storage utilisation

From SQL 2008 R2 SP 1 onwards the sys.dm_os_volume_stats dynamic management function returns the storage details for disk volumes:

It’s possible to generate various type of storage utilisation reports using this DMF.

For prior versions we would have to query the file systems using non-SQL methods (eg VBScript, CLR stored procedures, extended stored procedures). We’d combine this file system data with our database usage data to get an overall storage picture. Powershell along with the SQL Server Management Objects (SMO) provides another way to obtain this information. This post shows that the approach is not complicated and, as a bonus, provides us with a very flexible way of presenting the results. The code is as follows:


# Tested using Powershell V2

# SQLServer can be default instance, named instance or [server,port]

Param([string]$SQLServer = "YOURSERVER,1433")

$WinServer = ($SQLServer -split {$_ -eq "," -or $_ -eq "\"})[0]

# Fetch volume information

$VolumeTotalGB = @{Name="VolumeTotalGB";Expression={[Math]::Round(($_.Capacity/1GB),2)}}
$VolumeUsedGB = @{Name="VolumeUsedGB";Expression={[Math]::Round((($_.Capacity - $_.FreeSpace)/1GB),2)}}

$VolumeArray = Get-WmiObject -Computer $WinServer Win32_Volume | Where-Object {$_.FileSystem -eq "NTFS"} | `
Select-Object Name, $VolumeTotalGB, $VolumeUsedGB

# Fetch data and log file information

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null

$Instance = New-Object "Microsoft.SqlServer.Management.Smo.Server" $SQLServer

$FileTypeData = @{Name="FileType";Expression={"Data"}}
$FileTypeLog = @{Name="FileType";Expression={"Log"}}
$FileTotalGB = @{Name="FileTotalGB";Expression={[Math]::Round(($_.Size*1KB/1GB),2)}}
$FileUsedGB = @{Name="FileUsedGB";Expression={[Math]::Round(($_.UsedSpace*1KB/1GB),2)}}
$Volume = @{Name="Volume";Expression={}}

# Data

$FileArray = $Instance.Databases | Where-Object { $_.IsAccessible } | `
ForEach-Object { $_.FileGroups | ForEach-Object { $_.Files | Select-Object $FileTypeData, FileName, $FileTotalGB, $FileUsedGB, $Volume } }

#Log

$FileArray += $Instance.Databases | Where-Object { $_.IsAccessible } | `
ForEach-Object { $_.LogFiles | Select-Object $FileTypeLog, FileName, $FileTotalGB, $FileUsedGB, $Volume }

# Append volume information to data file information

$FileArray | ForEach-Object {
$FileName = $_.FileName
$_.Volume = $VolumeArray | Where-Object { $FileName -Match [regex]::Escape($_.Name)} | Sort-Object Name -Descending | Select-Object -First 1
}

# Sum the file size and space used by volume and file type and output the results

$SystemName = @{Name="SQLServer";Expression={$SQLServer}}
$VolumeName = @{Name="Volume";Expression={($_.Name -split ", ")[0]}}
$AggVolumeTotalGB = @{Name="VolumeTotalGB";Expression={($_.Group)[0].Volume.VolumeTotalGB}}
$AggVolumeUsedGB = @{Name="VolumeUsedGB";Expression={($_.Group)[0].Volume.VolumeUsedGB}}
$FileType = @{Name="FileType";Expression={($_.Name -split ", ")[1]}}
$AggFileTotalGB = @{Name="FileTotalGB";Expression={($_.Group | Measure-Object -Property FileTotalGB -Sum).Sum}}
$AggFileUsedGB = @{Name="FileUsedGB";Expression={($_.Group | Measure-Object -Property FileUsedGB -Sum).Sum}}
$AggFileCount = @{Name="FileCount";Expression={($_.Group | Measure-Object).Count}}

$FileArray | Group-Object -Property {$_.Volume.Name},{$_.FileType} | `
Select-Object $SystemName, $VolumeName, $AggVolumeTotalGB, $AggVolumeUsedGB, $FileType, $AggFileTotalGB, $AggFileUsedGB, $AggFileCount | Out-GridView

Please observe the line continuation back ticks.

There are very few actual executable lines in this script. The only parameter we set is the one for the SQLServer variable. Any backslash in the name (for a named instance) or comma (for a port separator) is stripped away to give us the Windows server name. This will work for clustered SQL Servers where the WinServer variable will take the virtual server name. The WMI Win32_Volume class is used to retrieve the capacity and free space for each NTFS volume. This class will cater for root volumes as well as well as mount points. We store the data in an array named VolumeArray

SMO is used to fetch data file and log file information (file name, size and space used). The IsAccessible property is checked to ensure that we process data for online databases only. This data is stored in an array named FileArray.

The only complicated part of the code is where we append the volume data to the file data. The idea is to create one array holding file as well as volume data. You can see that we’re aiming for the same result set we see for the screenshot for SQL Server 2012. The code achieves this by doing regex matches between the full file names and the volume names. The volume name will appear in the leading part of the full file name. If you are using mount points you’ll have two matches – one for the root volume (eg D:\) and another for the mount point (eg D:\SQLDATABASE\DATA). We obtain the mount point by doing a descending sort on matched volume names and selecting the first one. This matched volume’s data is stored in the Volume property (PSCustomObject) alongside all the other file properties for this file.

You could finish at this point and simply output FileArray to the screen, CSV, XML or HTML. The code listing goes a little further and does the equivalent of a SQL Server SUM(FileSize), SUM(FileUsedSpace) GROUP BY VolumeName, FileType. (FileType represents Data or Log). Group-Object and Measure-Object are very useful aggregation commandlets. I’m using them here to return just one of many possible result sets:

The script analyses database and log files only. Backup space utilisation could be analysed using a similar approach.

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: