SQLvariations: SQL Server, a little PowerShell, maybe some Hyper-V Rotating Header Image

Disk Space

Quick Blog: PowerShell Disk and MountPoint Check

Nov 24th, 2010
by Aaron Nelson.
4 comments

I tuned into twitter yesterday for a couple of minutes and found a great conversation going on between Nicolas Cain ( blog | twitter ) and Dave Levy ( blog | twitter ) about checking Disk Space & Mount Points. I really like what they were working on because it was actually one of my top priorities for the week. I already have some code for checking both regular disk drives and mount points with PowerShell but I was looking to improve it and get it ready for production monitoring.

Everyone’s environment is different and they build things based on needs and pain points. For my own environment I took a little of what Dave put together and a little of what Nick put together and built my own function for my environment. I’m still trying to add some more information to it but here’s what I’ve got so far:

Function Get-DisksSpace ([string]$Servername, $unit= "GB")
{
$measure = "1$unit"

Get-WmiObject -computername $serverName -query "
select SystemName, Name, DriveType, FileSystem, FreeSpace, Capacity, Label
  from Win32_Volume
 where DriveType = 2 or DriveType = 3" `
| select SystemName `
        , Name `
        , @{Label="SizeIn$unit";Expression={"{0:n2}" -f($_.Capacity/$measure)}} `
        , @{Label="FreeIn$unit";Expression={"{0:n2}" -f($_.freespace/$measure)}} `
        , @{Label="PercentFree";Expression={"{0:n2}" -f(($_.freespace / $_.Capacity) * 100)}} `
        ,  Label
}#Get-DisksSpace

The code above will create a PowerShell function (sorta kinda like a stored procedure only not really but just think of it like that if you’re a SQL person that’s new to PowerShell Smile ) Here’s how you would call it:

Get-DisksSpace “Win7NetBook” | Format-Table

Or if you only wanted to know about the drives that are low on space:

Get-DisksSpace “Win7NetBook” | where{$_.PercentFree -lt 20} | Format-Table

This will return you the list of drives and mount points on the machine you listed and default the unit of measure to convert the results to gigabytes. The great news is that this code runs really fast. Their conversation yesterday literally saved me hours of work. I’ll keep working with this and post another blog when I put the monitoring portion into production.

Checking Disk Space with PowerShell

Feb 28th, 2010
by Aaron Nelson.
Comments are off for this post

This is another one of the code examples that I did in my PowerShell for Data Professionals session last week.  If you give it your computer name and then run it you will get back a stream or properties like I mentioned during the session. 

get-wmiobject -query `
"Select DeviceID,Size,Freespace from Win32_logicaldisk where drivetype=3" `
-computer "YourComputerName"

Now this runs fine but it brings back quite a bit more information than we really need.  I mean we specified columns in our WQL statement and it brought back the kitchen sink almost as if we had given it a SELECT *.  Now I don’t know why this happens I just know that it does (I have emailed a few people but haven’t heard back yet).  To get around this problemfeature all you have to do is take the output from the command you executed and pipe it to the Select-Object cmdlet and specify the columns that you want again.

get-wmiobject -query `
"Select DeviceID,Size,Freespace from Win32_logicaldisk where drivetype=3" `
-computer "YourComputerName" | select-object DeviceID, Size, FreeSpace

And voilà you have now taken something from the pipeline and done something with it that hopefully any SQL person in the world can understand.  So what’s next?  Well just change -computer again to the name of your favorite Dev server and see how much disk space you have.

*Don’t forget the disclaimer about running other people’s scripts!

Here’s the link to the msdn article that will have more info on those properties and the methods that you can use against

http://msdn.microsoft.com/en-us/library/aa394173(VS.85).aspx