Learn more about SQL Server tools


Latest from MSSQLTips

Free SQL Server Learning

Get your SQL Server in shape this Summer with Spotlight on SQL Server

Encrypting Data with SQL Server

Physical and Virtual Performance Monitoring with SQL Diagnostic Manager

PowerShell script to find files that are consuming the most disk space

MSSQLTips author Jugal Shah By:   |   Read Comments (13)   |   Related Tips: More > PowerShell

As you know, SQL Server databases and backup files can take up a lot of disk space.  When disk is running low and you need to troubleshoot disk space issues, the first thing to do is to find large files that are consuming disk space.  In this article I will show you a PowerShell script that you can use to find large files on your disks.


To find the files I am using the Get-ChildItem cmdlet.


Get-ChildItem [[-path] ] [[-filter] ] [-include ] [-exclude ] [-name] [-recurse] [-force]

You can get the more information on the Get-ChildItem cmdlet, by executing the below commands.

## for detailed information
get-help Get-ChildItem -detailed
## For technical information, type:
get-help Get-ChildItem -full

Let's see a few examples of Get-ChildItem.

In this example, Get-ChildItem is retrieving a list of files and folders from the current location.

GCI Command

In this example, we will sort the output in descending order by name.

Get-ChildItem D:\Backup | sort-Object -property name -Descending

GCI Command Output

In this example, we will use the -recurse parameter to list the contents of folders and subfolders.

 Get-ChildItem d:\backup -recurse

GCI Command Recurse Output

You can use -include/-exclude parameter to retrieve or exclude files of specific criteria. To limit the number of rows of the output you can use -first [number of rows] (from top) OR -last [number of rows] (from bottom) parameter.

Get-ChildItem D:\MSSQL2K8\DATA\*.* -include *.ldf,*.mdf | select name,length -last 8


You can use the where-object cmdlet to retrieve information based on specific criteria. The where-object clause is enclosed within curly braces { } and the $_ notation is used to represent the object that is being transferred across the pipeline. Powershell uses the below operators for the comparison.

  • -lt Less than
  • -le Less than or equal to
  • -gt Greater than
  • -ge Greater than or equal to
  • -eq Equal to
  • -ne Not equal to
  • -like uses wildcards for pattern matching
Get-ChildItem D:\MSSQL2K8\*.* -include *.mdf | where-object {$_.name -like "M*"}

GCI Command Where-Object

You can use the this next script to find large files. In the script you have to specify the value of the $path (specify the path of the files to search), $size (will search files greater than or equal to the defined size), $limit (retrieve the top specified number of rows) and $Extension (search for the specific file extensions) parameters.

In this example, I am searching for the five largest files in folder "D:\Backup" and any subfolders, that are bigger than 100MB and have an extension of ".bak".

##Mention the path to search the files
$path = "D:\Backup"
##Find out the files greater than equal to below mentioned size
$size = 100MB
##Limit the number of rows
$limit = 5
##Find out the specific extension file
$Extension = "*.bak"
##script to find out the files based on the above input
$largeSizefiles = get-ChildItem -path $path -recurse -ErrorAction "SilentlyContinue" -include $Extension | ? { $_.GetType().Name -eq "FileInfo" } | where-Object {$_.Length -gt $size} | sort-Object -property length -Descending | Select-Object Name, @{Name="SizeInMB";Expression={$_.Length / 1MB}},@{Name="Path";Expression={$_.directory}} -first $limit

You can save the above script as filename.ps1.  I saved it as script3.ps1.  To execute the PowerShell script you can use ./ as shown below.

Powershell Script

Here is another script that you can use to export the result to a CSV file by using the Export-Csv parameter as shown below.

##Mention the path to search the files
$path = "D:\Backup"
##Find out the files greater than equal to below mentioned size
$size = 100MB
##Limit the number of rows
$limit = 5
##Find out the specific extension file
$Extension = "*.bak"
##script to find out the files based on the above input
$largeSizefiles = get-ChildItem -path $path -recurse -ErrorAction "SilentlyContinue" -include $Extension | ? { $_.GetType().Name -eq "FileInfo" } | where-Object {$_.Length -gt $size} | sort-Object -property length -Descending | Select-Object Name, @{Name="SizeInMB";Expression={$_.Length / 1MB}},@{Name="Path";Expression={$_.directory}} -first $limit
$largeSizefiles |Export-Csv c:\lsfreport.csv
Next Steps
  • Use this script to find the largest files on your server.  These may or may not be SQL Server files that are consuming all of your disk space.
  • Monitor file sizes and growth using this PowerShell script
  • Check out this other tip Powershell Script to delete file

Last Update: 9/24/2012

About the author
MSSQLTips author Jugal Shah
Jugal Shah has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2.

View all my tips
Related Resources

print tip Print  
Become a paid author

join MSSQLTips for free SQL Server tips     

Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates

       Note: your email address is not published. Required fields are marked with an asterisk (*)

Get free SQL tips:

*Enter Code refresh code     

Tuesday, August 12, 2014 - 4:28:45 PM - Ravi Read The Tip

Hello Jugal.

Thanks for the sharing the great information. I would like to know how can I modify the script

1. To look at more than one drive and

2. specifying parameter for days


Thank you in advance

Tuesday, September 17, 2013 - 4:43:31 PM - Daniel Leonard Read The Tip

This may be helpful.  I use the following to clean up backups for a TFS database.  It gets everything older than one month and removes it

$current = Get-Date;

$oneMonth = $current.AddMonths(-1);

$oldTextFiles = Get-ChildItem -Recurse -Filter *.txt | Where-Object {$_.LastWriteTime -lt $oneMonth};

$oldBakFiles = Get-ChildItem -Recurse -Filter *.bak | Where-Object {$_.LastWriteTime -lt $oneMonth};

You can then view the bak files sorted by length using the command (substitute 1kb for 1mb to see them in kilobytes)

$oldBakFiles | Sort-Object -Property Length | Select-Object FullName, @{Name="Length/MB"; Expression={"{0:N0}" -f ($_.Length/1mb)}}

$oldTextFiles | Sort-Object -Property Length | Select-Object FullName, @{Name="Length/MB"; Expression={"{0:N0}" -f ($_.Length/1mb)}}


To remove the files en masse, you can then use the following commands:

$oldTextFiles | Remove-Item

$oldBakFiles | Remove-Item


Thursday, May 16, 2013 - 7:33:26 AM - David Howell Read The Tip

It is useful to use the Export-Csv cmdlet here to output the details of all objects found to a CSV file for opening in Excel

Thursday, April 25, 2013 - 12:58:00 AM - Mohamed Irshad Read The Tip

Cool. LikeD it. Thanks a lot for sharing.

Tuesday, March 19, 2013 - 10:57:59 AM - Jugal Read The Tip

You can also try http://www.mssqltips.com/sqlservertip/2702/setting-the-powershell-execution-policy/ option in this kind of issue.

Tuesday, March 19, 2013 - 7:26:06 AM - Dinesh Read The Tip

Thanks a lot, saved my time................

Also would like to highlight the problem I faced while executing the script.... Powershell was not allowing to run the script.... Shows the following error:

File E:\Srpt.ps1 cannot be loaded because the execution of scripts is disabled on this system. Please see "get-help ab
ut_signing" for more details.
At line:1 char:11
+ ./Srpt.ps1 <<<<
    + CategoryInfo          : NotSpecified: (:) [], PSSecurityException
    + FullyQualifiedErrorId : RuntimeException

By using the below statement I have executed the script :

 powershell.exe -executionpolicy ByPass ./srpt.ps1

I am able to execute the script... Thanks once again...

Wednesday, October 10, 2012 - 8:36:08 AM - Giray Read The Tip

Nice script, thanks!

Monday, September 24, 2012 - 10:44:05 PM - Hassan Parthasarathy Read The Tip

Thanks for the nice cmdlet!!


Monday, September 24, 2012 - 10:34:15 AM - Craig A. Silvis Read The Tip

Thanks so much!  Just used this today on a couple of servers that were virtual machines with small C: drives that were running out of space.


Monday, September 24, 2012 - 9:52:13 AM - Satheesh Kumar Read The Tip

Hi Jugal,  I am following most of your posts, they are effective and helping me in my day to day work.  Keep up.

Monday, September 24, 2012 - 9:04:17 AM - Paresh Read The Tip

I really liked this posting.

Thank you so much for sharing.


Monday, September 24, 2012 - 8:48:44 AM - Rony Read The Tip




Monday, September 24, 2012 - 7:37:48 AM - Darek Read The Tip

Hi there. Nice stuff. One remark: Instead of using "-include", I'd use "-filter". I cannot remember exactly but I think (might be wrong) that "-filter" is faster.

Sponsor Information