Sql-server – Disk Space usage

disk-spacesql serversql-server-2012

Is there a way i can get a report or create a custom report in sql server 2012 which can update me with disk space available & actual disk space for listed drives twice or once a day.

I need this because my client requires to see the disk space usage over a month to see the desired pattern.

Thanks!

Best Answer

I have written reports, and actually alerting with it, for clients to monitor multiple servers within their test and production environments. I basically used a SQL Agent Job with a PowerShell step to pull in disk information (win32_volume) into some tables.

I then decided to create another PowerShell step that went back and checked if the free space was within a configured threshold (stored in a table). We went with this approach because it was just easier to setup and write fairly quickly. It was also cleaner code to write for a HTML report to be sent via email, than trying to do it in T-SQL.

You have DMVs in SQL Server to get disk space usage (e.g. sys.dm_os_volume_stats) but the PowerShell option above allowed us to monitor all drives found on a given server. Now caveat you will have to create a proxy account for the PowerShell execution and that account should have appropriate permissions.

An example of just getting a HTML report on free disk space.

EDIT

Just realized from Mike's comment that I had a function in my profile to, it is written a bit different than his but this is just what worked for me:

function Get-DiskSpace($server,[switch]$all)
{
        $servers = gc C:\Users\smelton\Documents\WindowsPowerShell\OSList.txt
        if ($all)
        {
            foreach ($s in $servers)
            {
                Get-WmiObject -Class Win32_Volume -ComputerName $s |
                    Select-Object @{Label='ServerName';Expression={$s}}, 
                        DriveLetter, 
                        Label, 
                        @{Label='GB Capacity';Expression={"{0:N2}" -f($_.Capacity/1GB)}},
                        @{Label='GB FreeSpace';Expression={"{0:N2}" -f($_.freespace/1GB)}},
                        @{Label='% Free';Expression={"{0:N2}" -f($_.freespace/$_.Capacity)}} |
                    Sort-Object -Property DriveLetter
            } #end foreach
        }
        else
        {
            Get-WmiObject -Class Win32_Volume -ComputerName $server | Where {$_.DriveType -eq 3} |
                Select-Object DriveLetter, Label, @{Label='GB Capacity';Expression={"{0:N2}" -f($_.Capacity/1GB)}},
                    @{Label='GB FreeSpace';Expression={"{0:N2}" -f($_.freespace/1GB)}},
                    @{Label='% Free';Expression={"{0:N2}" -f($_.freespace/$_.Capacity)}} |
                Sort-Object -Property DriveLetter
        } #end if/else
} #end Get-DiskSpace
Related Question