Over the weekend I was paged because a SQL Server volume violated a 5% minimum free disk space threshold set by our alerting system on storage volumes. This time additional storage had previously been requested but had not yet been provisioned. After going down the often unwise rabbit hole of shrinking one data file by only 3.91GB and afterward running Ola’s IndexOptimize to clean up the resulting fragmentation, a previously used cmdlet from the dbatools PowerShell module popped into my head – with a little help from PSReadLine’s CTRL+R to search through my command line history...
Found it!
Find-DbaOrphanedFile
I ran this command and quickly found 5 data files on the volume that were not in use - candidates for deletion - along with their corresponding transaction log files on another volume:
Caution: None of those “Are you sure you wish to delete this file?(Y/N)” safety prompts here…
Let’s dangerously pipe that to Remove-Item and poof! The files have been deleted.
Before:
Running the command to delete the orphaned MDF/NDF/LDF files…
After:
Nearly 10GB freed from one volume in a single line. Nice. And no fragmented indexes to think about, either.
Get-Help Find-DbaOrphanedFile -ShowWindow
offers an explanation of what the Find-DbaOrphanedFile cmdlet does:
I typically run Find-DbaOrphanedFile with the -RemoteOnly switch to display UNCs.
RunningGet-Help Find-DbaOrphanedFile -Examples
will offer some helpful examples.
It took me while to find out how to group the results in PowerShell to display the sum of the size of all orphaned files in a given directory, but this post from Jess Pomfret put it together for me.
Find-DbaOrphanedFile -SqlInstance localhost -RemoteOnly |
Get-Item |
Select-Object FullName, Directory, Length |
Group-Object Directory |
Select Name, @{Label='SizeGB';Expression={ ($_.Group.Length | Measure-Object -Sum).Sum/1GB }}
