Getting Disk Sizes using WMI and PowerShell

I love the 3rd party products that are on the market for SQL Server. Idera, Redgate and Quest (the big 3) have some amazing options for helping you manage your environment, but these can come at a pretty steep cost, and sometimes that budget just isn’t available to you. If you don’t have access to any of these tools, it can be a real challenge managing your environment, especially when you are dealing with thousands of databases across multiple instances.

PowerShell to the rescue. Here I go again… I have said it before and I will keep saying it, PowerShell is a very powerful tool, and once you get to know it, a pretty easy one as well.

One of the areas I have always had trouble with, is retrieving disk information. Before I knew PowerShell, I spent an inordinate amount of time writing overly complex T-SQL scripts to try and pull the data, but there were always barricades in the way (OLE not enabled, cmdshell disabled etc.) . Even VB scripts seemed to be a pain. Then I started playing with PS and WMI and realized I had been making my life a lot harder than it needed to be. With a simple one line script I could pull all the information I required:

Get-WmiObject Win32_LogicalDisk -Namespace "root\CIMV2" -Filter "DriveType=3" -ComputerName $server

I am not going to get into the details of WMI here (this is an excellent starting point, if you are interested), but I never realized quite how powerful it was.

The below script takes a list of servers in a simple text file, and iterates through each one using WMI to pull all of the available disks, their size and free space. There is a whole host of other information available in the WMI classes, but for the sake of the script, let’s keep it simple.

cls
#get the list of servers
$server_list = get-content 'c:\temp\servers.txt' | where-object{$_ -ne ''}
#loop through all of the servers individually
for($i=0;$i -le $server_list.length)
{
$server = $server_list[$i]
$server
$diskCol = Get-WmiObject Win32_LogicalDisk -Namespace "root\CIMV2" -Filter "DriveType=3" -ComputerName $server
foreach($disk in $diskCol)
{
#reset values, make sure nothing is carried across
$disk_name = ''
$disk_size = 0
$free_space = 0

#now get real values and then show them in the console
$disk_name = $disk.Name
$disk_size = [Math]::Round(($disk.Size / 1gb), 2 )
$free_space = [Math]::Round(($disk.FreeSpace / 1gb), 2 )

Write-Host "Drive : " $disk_name
Write-Host "Size : " $disk_size
Write-Host "free space : " $free_space
Write-Host

}
write-host
$i=$i+1
}

And there we have it. I am simply outputting the returned data to the console here, but you can do whatever you need to with the values, by adding a couple of SMO statements, you could feed the data back into a monitoring database, or you could output to an HTML report. Below is an example of the output I got for 2 of my servers:

Output

I hope this helps someone out in some small way, and thanks for reading.

Advertisements
This entry was posted in SQL Server and tagged , , , , . Bookmark the permalink.

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