Monitor SQL Server Disk Space Using PowerShell


By:   |   Updated: 2014-01-13   |   Comments (6)   |   Related: More > PowerShell


Problem

I work in an environment where getting new software approved is often quite difficult. We have a disk space monitor in place, but it doesn't provide the type of monitoring my team was looking for. Monitoring disk space is critical for DBAs In addition, we use mount points, which is not supported by our existing disk space monitor.

Solution

My solution is PowerShell centric. Many of you may wonder why a DBA would use PowerShell. I tend to use the following guidelines to determine whether I will use PowerShell or TSQL to perform a task.

  • TSQL - If the primary function of the task is set based (Select, Insert, Update, Delete), I use TSQL
  • PowerShell - If the task I am trying to perform has a significant amount of flow control, or needs to access objects that are not SQL, I tend to use PowerShell
Get Volume Information

Let's go get the information for the disks. I'm going to assume you have PowerShell 2.0 at a minimum installed on your server or workstation. If you don't have PowerShell installed, check here for instructions from the Scripting Guy Blog. Let's use the Integrated Scripting Environment (ISE) to do our work. You can find the shortcut in Start|All Programs|Accessories|Windows PowerShell. For Windows 8, search for "PowerShell ISE". The PowerShell 2.0 ISE will open up with a script window, a command window, and an output window. The PowerShell 3.0 ISE will open up with a script window, and a combined command/output window.

PowerShell does a fantastic job of accessing WMI objects. We are going to take advantage of this fact to get information about the volumes. Type the following command in PowerShell:

get-WMIObject Win32_Volume

Notice that you get a ton of information about the different volumes. This can be a bit overwhelming. Information about the information returned by Win32_Volume can be seen here. We can improve on the information displayed by piping it through the format-table Cmdlet.

get-WMIObject Win32_Volume | format-Table Name, Label, Freespace, `
                                          Filesystem, BlockSize, Capacity -Auto

This is a little better. By the way, notice the line continuation character which is the (`) usually above the tab key. We can still improve further, to do so we are going to use a filter.

$Filter = @{Expression={$_.Name};Label="DiskName"}, `
          @{Expression={$_.Label};Label="Label"}, `
          @{Expression={$_.FileSystem};Label="FileSystem"}, `
          @{Expression={[int]$($_.BlockSize/1KB)};Label="BlockSizeKB"}, `
          @{Expression={[int]$($_.Capacity/1GB)};Label="CapacityGB"}, `
          @{Expression={[int]$($_.Freespace/1GB)};Label="FreeSpaceGB"}
Get-WmiObject Win32_Volume | Format-Table $Filter -AutoSize

Here is a sample of the data I got by running the above:

DiskName Label       FileSystem BlockSizeKB CapacityGB FreeSpaceGB
-------- -----       ---------- ----------- ---------- -----------
C:\                  NTFS                 4        195          85
D:\                  NTFS                 4         98          68
G:\      DataVol2012 NTFS                 4        736         630
E:\      Filler      NTFS                 4         50          50
F:\      Backups     NTFS                64         85           0

Now let's turn the code into a simple function that we can use later. You will notice a couple of new things. First of all, I added a couple of parameters; ComputerName and Raw. One nice feature of Get-WmiObject is that you can run the command remotely against another computer. I also added a default for ComputerName to default to the local computer if no parameter is given.

The second parameter Raw is sort of a standard of mine. It allows you to either run the function to get information in a formatted table, or in raw form. Raw form is handy if you want to pass the data along to another function or script.

function get-DiskVolumes
{
    param(
        [string]$ComputerName=$env:COMPUTERNAME,
        [switch]$Raw
    )
    $Filter = @{Expression={$_.Name};Label="DiskName"}, `
              @{Expression={$_.Label};Label="Label"}, `
              @{Expression={$_.FileSystem};Label="FileSystem"}, `
              @{Expression={[int]$($_.BlockSize/1KB)};Label="BlockSizeKB"}, `
              @{Expression={[int]$($_.Capacity/1GB)};Label="CapacityGB"}, `
              @{Expression={[int]$($_.Freespace/1GB)};Label="FreeSpaceGB"}
    if($Raw){Get-WmiObject Win32_Volume -ComputerName $ComputerName | Select-Object $Filter}
    else{Get-WmiObject Win32_Volume -ComputerName $ComputerName | Format-Table $Filter -AutoSize}
}
Next Steps
  • Run the above script to load the function into memory.
  • Check the list of functions by typing dir function:
  • Run the function by typing get-DiskVolumes in the PowerShell_ISE command window.
  • Work with the function using the -ComputerName parameter to run on a remote computer.
  • Look at the difference in the output when using the -Raw switch parameter.
  • Coming Soon: Add the function to a module so you can load it easier.


Last Updated: 2014-01-13


get scripts

next tip button



About the author
MSSQLTips author Richard Vantrease Richard Vantrease is a lead SQL Server DBA for a large company. His specialties include architecture, the data engine and automation.

View all my tips
Related Resources





Comments For This Article




Monday, February 24, 2014 - 10:16:13 PM - Venky Back To Top (29558)

How to export the same results from powershell to a specifide sql table


Wednesday, January 29, 2014 - 3:55:48 PM - bass_player Back To Top (29275)

Here's an example which uses fewer lines of code, although not recommended for readability. This reads thru a text file that contains list of server hostnames, checks the disk free space and writes those that have free space less than 15% in a text file. The text file then gets sent as an email attatchment.

function getDiskFreeSpace

{

Get-WmiObject Win32_Volume -computername $args | Where {$_.DriveType -ne 5} | SELECT __SERVER, Name, @{Name="Size(GB)";Expression={"{0:N1}" -f($_.Capacity/1gb)}},@{Name="FreeSpace(GB)";Expression={"{0:N1}" -f($_.freespace/1gb)}},@{Name="FreeSpacePerCent";Expression={"{0:P0}" -f($_.freespace/$_.capacity)}} |  Where-Object -FilterScript {$_.FreeSpacePerCent -lt 15} | Sort-Object -property "FreeSpacePerCent" | FT

}

 

ForEach($s in Get-Content serverlist.txt) 

{

getDiskFreeSpace $s | Out-File C:\DBA\diskFreeSpaceResults.txt -append

}

 

#Call function

get-Date | Out-File C:\DBA\diskFreeSpaceResults.txt -append

 

# Send results of script in email

Send-MailMessage -to "[email protected]" -from "[email protected]" -subject "Daily Disk Free Space Report" -Attachment "C:\DBA\diskFreeSpaceResults.txt" -SmtpServer mailhost.testdomain.com

 

PowerShell v4 now has Get-Volume cmdlet that makes this a lot easier

http://technet.microsoft.com/en-us/library/hh848646.aspx


Thursday, January 23, 2014 - 6:39:38 PM - Richard Vantrease Back To Top (28202)

Thank you for the feedback.

CVN RAO - Take a look at Shafiq's update in the comments.  It uses a list of servers in a file to check multiple servers.

Result = @()

foreach($server in (gc .\servers.txt)) {
 $Result += ( get-DiskVolumes -ComputerName:$server )
 }
$Result > c:\ServerSpace.txt
Invoke-Item c:\ServerSpace.txt


Thursday, January 16, 2014 - 5:20:41 PM - cvn rao Back To Top (28108)

Really very good blog.. Just want to know when we run it on multiple servers at single shot, do we need to have a list of server names in a text file?

 

 

Thanks

 

Rao


Tuesday, January 14, 2014 - 2:40:47 PM - TimothyAWiseman Back To Top (28072)

This is a very useful function.  Thank you for providing it.

You mentioned that it might be surprising to some that you use powershell as a DBA.  But I don't find it suprising at all.  There are lots of good articles on why SQL DBAs might want to learn PowerShell, but my favorite is this one from Laerte: "I'm a SQL Server DBA and I'm in Love with Powershell."


Monday, January 13, 2014 - 3:01:48 PM - Shafiq Back To Top (28044)

Thanks Richard for this valuable code, I took your code and extend it to run for multiple servers in one go

function get-DiskVolumes
{
    param(
        [string]$ComputerName=$env:COMPUTERNAME,
        [switch]$Raw
    )
    $Filter =
       @{Expression={$ComputerName};Label="Server"}, `
       @{Expression={$_.Name};Label="DiskName"}, `
              @{Expression={$_.Label};Label="Label"}, `
              @{Expression={$_.FileSystem};Label="FileSystem"}, `
              @{Expression={[int]$($_.BlockSize/1KB)};Label="BlockSizeKB"}, `
              @{Expression={[int]$($_.Capacity/1GB)};Label="CapacityGB"}, `
              @{Expression={[int]$($_.Freespace/1GB)};Label="FreeSpaceGB"}
    if($Raw){Get-WmiObject Win32_Volume -ComputerName $ComputerName | Select-Object $Filter}
    else{Get-WmiObject Win32_Volume -ComputerName $ComputerName | Format-Table $Filter -AutoSize}
}

 

$Result = @()

foreach($server in (gc .\servers.txt)) {
 $Result += ( get-DiskVolumes -ComputerName:$server )
 }
$Result > c:\ServerSpace.txt
Invoke-Item c:\ServerSpace.txt

 

servers.txt is in My Document folder and contains the list of servers i like to see the output



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