Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (6)   |   Related Tips: More > PowerShell

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

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.

Solution

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:

CREATE TABLE Inventory 
(
   [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 = [system.management.automation.runspaces.initialsessionstate]::CreateDefault()
$RunspacePool = [runspacefactory]::CreateRunspacePool(1, $MaxThreads, $ISS, $Host)
$RunspacePool.Open()
$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%'
      BEGIN
         SELECT DISTINCT LEFT([filename], CHARINDEX('\', [filename]) - 1) FROM [dbo].[sysaltfiles]
      END
      ELSE
      BEGIN
         SELECT DISTINCT LEFT([physical_name], CHARINDEX('\', [physical_name]) - 1) FROM [sys].[master_files]
      END"
   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.Dispose()
      $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"
      break
   }
   #sleep
   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.

output
Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Pablo Echeverria I've worked for more than 10 years as a software programmer and analyst. Last year I switched jobs to a DBA position, where I've been suited to implement new processes and optimize existing ones.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, November 08, 2017 - 4:53:59 PM - Pablo Echeverria Back To Top

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 08, 2017 - 12:55:33 PM - Victor Back To Top

 Pablo.

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 08, 2017 - 10:04:52 AM - Pablo Echeverria Back To Top

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 08, 2017 - 9:45:10 AM - Pablo Echeverria Back To Top

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 07, 2017 - 4:12:58 PM - Victor Back To Top

Hi.

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 07, 2017 - 3:21:32 PM - Jan Arnoldus Back To Top

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?

Thanks.


Learn more about SQL Server tools