Disk Space usage

Question

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!

Answer 1

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

Answer 2

Take a look at the data collector option. Specifically the Disk Usage collection set. The data collector allows you to schedule the collection of various types of information (in your case disk usage) and store them in something called the Management DataWarehouse. Once there it's easy enough to query and/or write reports on the data. This is part of SQL Server 2008 and up.

If the built in Disk Usage collection set doesn't have the information you need you can also build your own collection sets. In this case you could pull information using sys.dm_io_virtual_file_stats (assuming the drives you want have database files on them) or some other custom code.

Here are some BOL links to get you started.

Introducing the data collector

Data Collection

SQL Server performance monitoring with Data Collector

Answer 3

This isn't a direct answer to your question, since what you want is trend analysis, but you should familiarize yourself with the reports in SSMS at the different node levels. They are much-improved over previous versions. There's one at the database level that shows size of individual tables.

Look into the Management Data Warehouse, as well.