By: Alejandro Cobar | Comments (2) | Related: > Monitoring
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
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
- Monitoring SQL Server with PowerShell Core Object Setup
- Monitoring SQL Server with PowerShell Instance Data Collection
- Monitoring SQL Server with PowerShell Instance Jobs Collection
- Monitoring SQL Server with PowerShell Instance Jobs Last Execution
- Monitoring SQL Server with PowerShell Instance Backups
- Monitoring SQL Server with PowerShell Instance Database and Database Files
About the author
Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
