5

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!

  • 4
    This should be pulled outside of SQL Server. Manage operating system things in the operating system in my opinion. Use SQL Server to store the data, by all means, and create a report off of the data. But don't pull the data from within SQL Server. – Thomas Stringer Jan 15 '15 at 14:19
  • It's maybe not exactly what you need but if you run "sp_helpdb" you get the actual database sizes, including tempdb. And I agree this should be done from outside, any monitoring tool helps you to keep track of diskusage AND saves that data over time. – RayofCommand Jan 15 '15 at 14:20
  • 2
    Is there at least one data or log file on each of the drives in the list? If so you can get the info from a new DMV, but in either case I still agree with Thomas - why is this SQL Server's job? – Aaron Bertrand Jan 15 '15 at 14:21
  • Also you can see this, dba.stackexchange.com/questions/29543/… – jhdrosos Jan 15 '15 at 14:24
  • the following website contain some tips about this topic mssqltips.com/sqlservertip/2444/… – Ahmad Abuhasna Jan 15 '15 at 14:25
8

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
|improve this answer|||||
  • 1
    You could also schedule PowerShell without bothering with SQL Server Agent. – Aaron Bertrand Jan 15 '15 at 15:51
  • That is true, the client in this situation wanted to know when the script failed to run and SQL Agent provided the easiest method for that. However, I also included error handling in the PowerShell that wrote to a table as well just to cover all the bases. – user507 Jan 15 '15 at 16:01
  • Shawn@ Thanks, i will look for Powershell scripts, though i am not much familiar on using them – KASQLDBA Jan 15 '15 at 16:13
  • Added link to a good script for building a similar report, starting point. – user507 Jan 15 '15 at 17:39
  • I've used a report similar to this one to monitor multiple servers. You can easily get it to write the results to a table to start tracking growth. – Your_comment_is_not_funny Jan 15 '15 at 21:30
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

|improve this answer|||||
  • I believe Data collection would result in creating a database in production env? So i am not sure if this can be implemented.. – KASQLDBA Jan 15 '15 at 16:14
  • Actually the recommendation I read said you should create the database in a non-production environment. Data Collector is designed to pull data from 1 or more instances and save the data in a single location. There is no problem collecting the data from your production environment and saving it in a test environment. – Kenneth Fisher Jan 15 '15 at 16:20
  • Okay, if i can use the Data collector on test to connect to my prod env, then i should be good to go, thanks! – KASQLDBA Jan 15 '15 at 16:24
0

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.

|improve this answer|||||
  • There is nothing in the OP's question that talks about space regarding tables. – user507 Jan 15 '15 at 22:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.