Wednesday, 7 March 2012

Disk space alarms for Exchange 2007/2010 and MS SQL Server 2008 using PowerShell

Disks fill up, and when they do, bad things happen. If the data disks for the mail or database servers fill up, mail stops and the production halts. Not good.

Fortunately, it's possible to get an early warning for Exchange 2007/2010 and SQL Server 2008 R2, using PowerShell. Both server products come with their customized PowerShell modules, which you'll need to use to make the magic happen.

Obligatory disclaimer: I am not a coder and i'm a PowerShell neophyte. I'm most likely making stoopid noobie errors here, but at least i get the job done with this.

The first question to have answered when monitoring disk space is which disk volumes should i be watching? The system drive and the drive(s) with the databases or mailboxes, of course. But which disks are they on?

There are two ways to answer that question. Either you go to the Exchange or SQL Server management interface and browse, or you create a script which asks for the information and works from there. As the second option creates a portable solution, that's the path for me.


Let's begin with the Exchange server. The following will list of the names and database files for the mailbox databases on the server:

Get-MailboxDatabase | select Name,EdbFilePath

If you have a slightly bigger organization you'll probably have more than one database, otherwise you'll end up with a single line answer.

Then you iterate through the list to get the drive the database file is on and ask WMI how much space there is free and in total on the disk:

Get-MailboxDatabase | select Name,EdbFilePath | foreach {   $drive = $_.EdbFilePath.DriveName  $disk = Get-WmiObject Win32_LogicalDisk -filter "DeviceID='$drive'"  $free = 100 * $disk.FreeSpace / $disk.Size}

We now need to check whether "free" is below an alarm limit, so add this to the top of the script

$alarm = 10

...and this before the last bracket...

if ($free -lt $alarm) {  $msg = $_.Name + " @ $drive {0:n1}% free" -f $free  $msg}

This would output a warning for each volume containing an Exchange mail store with less than ten percent free space (with one decimal, that's what the {0:n1} stuff does).

If you'd rather have the message go to the Event log, then change the last $msg line to

Write-EventLog -logname Application -source Application -eventID 42000 -entrytype Warning -message $msg

Change the values for -source and -eventID to something which makes sense for your system. I'm no Event log guru, so i do not know what values are sensible or not. Since we use Kaseya VSA on the systems we monitor, my source is KaseyaCollector.

SQL Server

The story is pretty much the same on SQL server, except that you need to run it on the SQL PowerShell interface, sqlps.exe

First, you connect to the database server's management bits and from there on it's much like with the Exchange server script. There is one ugly hack that i'm particularly ashamed of, and that's when i typecast the Root property into a string to strip off the trailing slash from it so that i can use it in the WMI query. I'm sure it's possible to get the information without throwing out the semantics, but i haven't found it yet. Suggestions welcome.

$alarm = 10$srv = New-Object Microsoft.SqlServer.Management.SMO.Server("(local)")$srv.Databases | select Name,PrimaryFilePath | foreach {  $vol = Get-ItemProperty -path $_.PrimaryFilePath | select Root  $did = $vol.Root.Name.substring(0,2)  $disk = Get-WmiOhject Win32_LogicalDisk -filter "DeviceID='$did'"  $free = 100 * $disk.FreeSpace / $disk.Size  if ($free -lt $alarm) {    $msg = $_.Name + " @ $did {0:n1}% free" -f $free    Write-Eventlog -logname Application -source Application -eventID 42002 -entrytype Warning -message $msg  }}

Up next: Automating it all

In a future posting, i'll write how to automate this process. If you're impatient, google how to schedule Exchange 2007/2010 PowerShell scripts. It's not hard but it's tricky.