How To Check Disk Space in SQL Server

Problem

As SQL Server DBAs, we need to be aware of what is occurring within the database server and outside the database server to ensure SQL Server is running without issue. One of the tasks is to monitor free space on the drives where SQL Server files reside and in this tutorial, we look at a way to easily capture all of this data from all of your instances for easy analysis and reporting.

Solution

Within this module, I present a PowerShell script that collects basic storage information from all the instances under your support. With this solution, you can build a proactive alerting mechanism (as frequent as you want) to stay on top of the game.

Prerequisites

In order to use this module, you need to create the core objects found here.

Database Objects

For this particular module, only one table will be created (in case it doesn’t exist), and it is the table to store the information of the disk drives, from each instance. If you want to add more fields to this table, make sure to adjust the structure within the PowerShell script and adapt the respective logic that will handle that additional field(s).

inventory.Storage
- serverId: serverid ties back to inventory.MasterServerList
- drive: the drive letter serving the instance.
- total_space: the amount of disk space of the drive.
- free_space: the amount of free disk space left.
- percent_free_space: the percent of free disk space available.
- data_collection_timestamp: the date time value when the information was collected from the instance.

*The total and free space values are expressed in Megabytes.

PowerShell Script

The PowerShell script that creates the above object and inserts data into the inventory.Storage table is called:

Get-MSSQL-Instance-Storage.ps1

The script has some validations that will help you check if some key elements are missing for the script to run successfully. For instance, it will confirm that the inventory.MasterServerList table exists and that it has at least 1 active instance registered to be able to have something to work with.

If you have followed along the other modules, you will notice that we have been storing all objects in "C:\temp", but you can use any folder you want. If you make a change to the central folder location, you will need to edit the first line in the following PowerShell script to specify the new folder location.

How to Use

Navigate to the folder where you created the files and you can run the PowerShell script as follows:

Option 1

Right click on Get-MSSQL-Instance-Storage.ps1 and select Run with PowerShell

Option 2

Open a command window and navigate to the folder where you saved the above files and run

powershell "C:\temp\Get-MSSQL-Instance-Storage.ps1"

Option 3

Schedule this as a SQL Server Agent Job to run on a regular basis.

Option 4

Schedule this as a Windows Task Scheduler job to run on a regular basis.

Check Creation of Database and Objects

After running the PowerShell script, we can see the objects that are created.

If we query the inventory.Storage table, we can see the data that has been collected.

This is the screenshot of the C:\ drive in my VM so that you can see that the captured results are correct:

You can see that the drive, total_space, free_space and percent_free_space values are all the same for my 2 instances. This is because I have them in the same server.

Let me give you one more example to showcase the usefulness of this script. Let me add an extra drive in my VM, with drive letter S:\, and I’m going to create a new dummy database whose datafiles will be placed in that drive; after doing that and re-running the script, this is what I get:

Important Notes

The PowerShell script will store only the information from the last execution, no historical data is retained. If you’d like to keep information from previous executions, you would have to modify the script and adapt it to your particular use case.
The script will gather information only from disks that contain SQL Server data files, so for instance, if in your server you have additional disk drives used for other purposes, then those won’t be captured.

Checking for Errors

To check for errors query the monitoring.ErrorLog table using the following query:

SELECT *
FROM monitoring.ErrorLog
WHERE script = 'Get-MSSQL-Instance-Storage'

If you’d like to know the SQL Server instance that got the errors, you would have to issue the query like this:

SELECT 
   CASE WHEN msl.instance = 'MSSQLSERVER' THEN msl.server_name ELSE 
      CONCAT(msl.server_name,'\',msl.instance) END AS instance, 
   e.script,
   e.message,
   e.error_timestamp
FROM monitoring.ErrorLog e
JOIN inventory.MasterServerList msl ON msl.serverId = e.serverId
WHERE e.script = 'Get-MSSQL-Instance-Storage'

Useful Queries

Are there any data files stored in drive C:\?

Let’s keep in mind that storing data files on the C:\ drive is a bad practice.

SELECT *
FROM inventory.Storage
WHERE drive = 'C:\';

Display the list of disk drives sorted from low to high.

*This will allow you to immediately know the space distribution across all the instances under your support.

SELECT *
FROM inventory.Storage
ORDER BY percent_free_space;

Which disk drives have entered a warning/critical threshold?

*Assuming Warning <= 10%.

SELECT *
FROM inventory.Storage
WHERE percent_free_space <= 10;

Get a list of the disk drives grouped by servers instead of instances.

SELECT 
   msl.server_name,
   s.drive,
   s.total_space,
   s.free_space,
   s.percent_free_space      
FROM inventory.Storage s
JOIN inventory.MasterServerList msl ON s.serverId = msl.serverId
GROUP BY msl.server_name,s.drive,s.total_space,s.free_space,s.percent_free_space;

Download Scripts

Download the scripts for this module

Next Steps

With this information, you can craft a reporting/alerting mechanism that can perfectly work on a daily basis. I have implemented this on the current production infrastructure that I support, and I can assure you that It is something very useful.
Check out the other parts of this series

Resources

Community

Subscribe