Find Drives with Low Disk Space for all SQL Server Instances using PowerShell

By:   |   Comments (8)   |   Related: > PowerShell


We need to ensure the availability of the databases across several SQL Server instances. If there’s an issue, we must know before our customers are impacted. One of the things we must check is free space per drive, and there’s no easy way to monitor it as we need to check each instance one by one. Even automating this task can take several minutes to complete, in my case it was taking about 15 minutes to query 170 instances, mainly because the inventory contains outdated entries that no longer exist.


By implementing the script below, we can take advantage of the parallelism of tasks to gather the information, completing in 20 seconds for the 170 instances, without having to wait for the previous instance to continue. At the end of the process we get a list of unresponsive instances that must be checked manually or removed from the inventory.

PowerShell Check Disk Space Script in Parallel for all SQL Server Instances

First we need to configure our environment, specifying the server, database and query where we have our inventory.  The minimum details needed are the ServerName and InstanceName, but having the Environment is also useful. Then, we specify the free space threshold for the report, in my case I set it up to report anything below 20%. Finally, the Maxthreads indicates I’ll have 20 threads gathering data across all of the instances.

I created a table called Inventory on my central server as follows:

   [Environment]  nvarchar(128) NOT NULL,
   [ServerName]   nvarchar(128) NOT NULL,
   [InstanceName] nvarchar(128) NOT NULL

Then I added some records to the table as follows.  The InstanceName needs to include both the server and instance name.

  • NoServer - this is a dummy server to show the error
  • ServerA - this is the default instance
  • ServerB - this uses a named instance called Test1
INSERT INTO Inventory values ('Bad','NoServer','NoServer')
INSERT INTO Inventory values ('Production','ServerA','ServerA')
INSERT INTO Inventory values ('Test','ServerB','ServerB\Test1')

Following is the PowerShell script.  You will need to adjust the following variables:

  • $server - this is the central SQL Server instance where you are storing the list of servers. 
  • $database - this is the database where you are storing the table that has the list of servers
  • $query - this is the code that queries the table with the server names
  • $threshold - this determines the threshold for % free to report back
  • $MaxThreads - this is the number of parallel queries to run
$ErrorActionPreference = "Stop" #stop when an error is encountered
# Declare variables
$server = "ServerC"
$database = "master"
$query = @"
SELECT [Environment], [ServerName], [InstanceName]
  FROM [Inventory]
$threshold = 20 #less than 20% is reported
$SleepTimer = 1000 #after X milliseconds, check if the jobs have finished. 1000 is every second.
$MaxResultTime = 300 #after X seconds, all jobs are killed. 300 is 5 minutes.
$Maxthreads = 20 #number of parallel jobs
# import modules
Import-Module SqlPs -DisableNameChecking
$error.clear() #clear error generated by last command
# get list of instances
$objects = @(Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query)
#environment setup
$output = @()
$errors = ""
$Jobs = @()
$ISS = []::CreateDefault()
$RunspacePool = [runspacefactory]::CreateRunspacePool(1, $MaxThreads, $ISS, $Host)
$script = {
   Param ([string]$environment, [string]$serverName, [string]$instanceName, [int]$threshold)
   $ErrorActionPreference = "Stop"
   $query = "
      SET NOCOUNT ON --No 'rows returned' message
      IF @@VERSION LIKE 'Microsoft SQL Server  2000%'
         SELECT DISTINCT LEFT([filename], CHARINDEX('\', [filename]) - 1) FROM [dbo].[sysaltfiles]
         SELECT DISTINCT LEFT([physical_name], CHARINDEX('\', [physical_name]) - 1) FROM [sys].[master_files]
   try {
      $disks = sqlcmd -S $instanceName -Q $query -h-1 -W -b -r1 2>&1 #no header, no whitespaces, break on error, errors to output, stderr to stdout
      if (!$disks -or $disks.length -eq 0) { return $instanceName + ": Unable to query server" | Out-String } #server may not exist anymore
      $diskList = "" #filter volumes based on drive letter
      foreach ($row in $disks) {
         if ($diskList -eq "") { $diskList = "DriveLetter='$($row)'" }
         else { $diskList += " OR DriveLetter='$($row)'" }
      $result = Get-WmiObject Win32_Volume -ComputerName $serverName -filter "$diskList" | select `
         @{Name="Environment"; Expression={$environment}}, `
         @{Name="InstanceName"; Expression={$instanceName}}, `
         DriveLetter, `
         Label, `
         @{Name="Capacity"; Expression={[math]::Round($_.Capacity/1GB, 2)}}, `
         @{Name="FreeSpace"; Expression={[math]::Round($_.FreeSpace/1GB, 2)}}, `
         @{Name="PercentFree"; Expression={[math]::Round(($_.FreeSpace * 100 / $_.Capacity), 2)}}
      "" #indicate there was no error
      $result | where {$_.PercentFree -lt $threshold} #return results lower than threshold
   catch {
      return $instanceName + ": " + $LastExitCode + " " + $_ | foreach { $_.ToString() } | Out-String #Get-WmiObject error, maybe permissions
function CreateThread() {
   param ([string]$environment, [string]$serverName, [string]$instanceName, [ref]$Jobs)
   $PowershellThread = [powershell]::Create().AddScript($script) #scriptToRun
   $PowershellThread.AddArgument($environment) | out-null
   $PowershellThread.AddArgument($serverName) | out-null
   $PowershellThread.AddArgument($instanceName) | out-null
   $PowershellThread.AddArgument($threshold) | out-null
   $PowershellThread.RunspacePool = $RunspacePool
   $Handle = $PowershellThread.BeginInvoke()
   $Job = "" | select Handle, Thread, object
   $Job.Handle = $Handle; $Job.Thread = $PowershellThread
   $Jobs.value += $Job
$ResultTimer = Get-Date #start time
#start processing first task for each instance
for ($i=0; $i -lt $objects.length; $i++) {
   CreateThread $objects[$i]."Environment" $objects[$i]."ServerName" $objects[$i]."InstanceName" ([ref]$Jobs)
while (@($Jobs | where {$_.Handle -ne $Null}).count -gt 0) {
   #update completed jobs, get errors and result, and dispose them
   foreach ($Job in @($Jobs | where {$_.Handle -ne $Null -and $_.Handle.IsCompleted -eq $True})) {
      $results = $Job.Thread.EndInvoke($Job.Handle)
      if ($results[0] -and $results[0] -ne "") {
         $errors += $results[0]
      $output += $results[1]
      #end thread
      $Job.Thread = $Null
      $Job.Handle = $Null
   #show progress
   $inProgress = @($Jobs | where {$_.Handle.IsCompleted -eq $False}).count
   Write-Progress `
      -Activity "Gathering data" `
      -PercentComplete (($objects.length - $inProgress) * 100 / $objects.length) `
      -Status "$inProgress pending"
   #exit on timeout
   $currentTime = Get-Date
   if (($currentTime - $ResultTimer).totalseconds -gt $MaxResultTime) {
      Write-Error "Child script appears to be frozen, try increasing MaxResultTime"
   Start-Sleep -Milliseconds $SleepTimer
#dispose thread pools
$RunspacePool.Close() | Out-Null
$RunspacePool.Dispose() | Out-Null
$errors #return errors
$output | Format-Table -Auto #return results

Work performed by each thread

There is a variable named $script, which performs the following:

  1. Query the instance to get the drives where any databases reside. This is because we or someone prior may have misconfigured a database. Note that Invoke-Sqlcmd is not thread safe, so we have to use SQLCMD with a special setup: remove “rows returned” message, do not return headers, and do not return white spaces. The query executed works for any version of SQL Server after SQL Server 2000. If we don’t get any results, we return an error to check the instance manually.
  2. For each drive returned from the query, we construct a filter to be passed to the next step, so we won’t query all the disks for a server, but only the ones we’re interested in. This is because multiple instances can reside on the same server, and it’s a waste of resources and time to query all of the drives if we’re not interested in them.
  3. Get the information about the disks, but express the values in GB rounded to 2 decimals.
  4. Return the information where the percent free is less than the threshold specified at the beginning of the script.

When this is run, the script shows the progress and the remaining instances:

status bar

At the end, the script reports any instance that could not be queried (due to a SQL or a Windows error) and the ones that have less than the threshold free space.  We can see below that the first server had an error and the other two servers returned information.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Pablo Echeverria Pablo Echeverria is a talented database administrator and C#.Net software developer since 2006. Pablo wrote the book "Hands-on data virtualization with Polybase". He is also talented at tuning long-running queries in Oracle and SQL Server, reducing the execution time to milliseconds and the resource usage up to 10%. He loves learning and connecting new technologies providing expert-level insight as well as being proficient with scripting languages like PowerShell and bash. You can find several Oracle-related tips in his LinkedIn profile.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, November 27, 2017 - 7:57:51 AM - Pablo Echeverria Back To Top (73286)

Hi Rod, yes, I'd create a query with several INSERT statements, and use Invoke-SqlCmd the same way it is used to get the list of servers, to store it in the database.

Wednesday, November 22, 2017 - 10:49:19 AM - Rod Back To Top (70094)

I have done the basics with powershell. Is there a way to store the results inside of the database instead of just displaying the informaiton on the screen?


Wednesday, November 8, 2017 - 4:53:59 PM - Pablo Echeverria Back To Top (69451)

Hi Victor, yes you're right, this script is only for data and log files, not every drive on the server.

For example, if we have a cluster with drives C: for the OS, P: for the pagefile, Q: for the quorum, and there are installed 5 different instances, you're not going to get unuseful or repeated information for each instance. But if this is not the case for you, you're ok listing all the drives and their information :)

Wednesday, November 8, 2017 - 12:55:33 PM - Victor Back To Top (69444)


I ran that SQL statement and it shows more than one drive in SSMS. But now that I looked at it, it only pulls the info from sys.master_files table, which means it only pulls info about the locations of database and log files wherever they are stored, the info from master database. It doesn't show ALL the drives on that server and it never will. If I don't keep any data or logs on C drive, your script will never show the space info about C drive.

I am using another prroach to get the onfo about ALL drives.


Function Get-DisksSpace ([string]$Servername, $unit= "GB")


$measure = "1$unit"


Get-WmiObject -computername $Servername -query "

select SystemName, Name, DriveType, FileSystem, FreeSpace, Capacity, Label

  from Win32_Volume

 where DriveType = 2 or DriveType = 3" `

| select @{Label="SystemName";Expression={$Servername.ToUpper()}} `

        , Name `

        , Label `

        , @{Label="SizeIn$unit";Expression={"{0:n2}" -f($_.Capacity/$measure)}} `

        , @{Label="FreeIn$unit";Expression={"{0:n2}" -f($_.freespace/$measure)}}



Wednesday, November 8, 2017 - 10:04:52 AM - Pablo Echeverria Back To Top (69438)

Hi Jan, thanks for your comments!

I strongly believe everything should be centralized, either in a single monitoring server or through Msx/Tsx, so you have control and ease of deployment.

The monitoring results can be sent by email, i.e. every 4 hours, or there should be a dashboard you can check anytime. But I agree with you, if there is an issue, a problem will occur and you won't notice it soon enough unless you shorten your interval (which will consume more of your time reading and interpreting the results) or make your code smarter (like you did).

About trusted connection, yes these scripts use trusted connection, because I launch Powershell with an elevated account, and when it's converted to a SQL job, it will run under the SQL Server Agent account.

If you're worried about your PS code, I'd suggest creating an .exe using Visual Studio (code similar to PS) with obfuscation, but you must know even this won't guarantee it can't be decoded and tampered, so you better put extra security and auditing on your monitoring server, which reinforces the concept of having a single monitoring server.

Wednesday, November 8, 2017 - 9:45:10 AM - Pablo Echeverria Back To Top (69437)

Hi Victor, can you try the following?

1. Run this code in your server, to confirm all drives are listed: SELECT DISTINCT LEFT([physical_name], CHARINDEX('\', [physical_name]) - 1) FROM [sys].[master_files]

2. Run this command in a Powershell prompt against your server: Get-WmiObject Win32_Volume -ComputerName $serverName

I'm thinking it may be related to permissions, or the information is not being returned from Get-WmiObject.

Tuesday, November 7, 2017 - 4:12:58 PM - Victor Back To Top (69388)


Thank you for the script. But for some reason the script returns only 1 drive, even when I set the threshold to 100, which in that case should return all the drives.


Tuesday, November 7, 2017 - 3:21:32 PM - Jan Arnoldus Back To Top (69384)

Thanks for sharing. For as much as I dislike PS scripts, this is the Rolls Royce of disk space checking. 

Just for discussion purposes, what is the best way to manage large number of instances using DIY scripts?

Watching the execution of this script or reading its output can be time-consuming, I imagine. 

I used to check against preset thresholds but found it less informative because I could not gain a sense of how my files are growing. I later adopted an intelligence-oriented approach where my script alerts me whenever free space drops by more than 2%. While doing so, the script also sends me a list of data files on the affected disk partition, as well the growth size of each file, so that I know that when any or all of them grow again, whether a partition is in danger of being exhausted.

Out of curiosity, are you running sqlcmd using trusted connection? Can PS script be encrypted to avoid being tempered with?


get free sql tips
agree to terms