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


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

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

Free MSSQLTips Webcast Today >> Optimize SQL Server Performance


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:

signup button

next tip button

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

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, July 08, 2015 - 2:26:56 PM - Scott Back To Top

An alternative would be to query the master_files table to get the sizes of all active files.  This won't tell you anything about any other large files on the server, but it's easier to run remotely.

Invoke-Sqlcmd -ServerInstance servername -Query "SELECT physical_name, sizeMB = size / 128.0 FROM sys.master_files ORDER BY size DESC"

Or if you like you file sizes formatted with thousands separators:

Invoke-Sqlcmd -ServerInstance servername -Query "SELECT physical_name, sizeMB = CONVERT(VARCHAR, CAST(size / 128.0 AS MONEY), 1) FROM sys.master_files ORDER BY size DESC"

Wednesday, July 08, 2015 - 1:52:23 PM - Denis.V Back To Top


Thanks a lot for good script, but it cuts long names and paths and I decided to little correct it and provide my version to resolve this issue:

##Mention the path to search the files

$path = "C:\"

##Find out the files greater than equal to below mentioned size

$size = 1MB

##Find out the specific extension file

$Extension = "*.bak", "*.dif","*.trn","*.mdf","*.ldf","*.ndf"

##script to find out the files based on the above input

get-ChildItem -path $path -recurse  -include $Extension | where-Object {$_.Length -gt $size} | 

sort-Object -property length -Descending |  Format-Table  directory, @{Name="File Name";Expression={$_.Name}}, @{Name="SizeInMB";Expression={$_.Length / 1MB}} -Autosize

Tuesday, August 12, 2014 - 4:28:45 PM - Ravi Back To Top

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 Back To Top

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 Back To Top

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 Back To Top

Cool. LikeD it. Thanks a lot for sharing.

Tuesday, March 19, 2013 - 10:57:59 AM - Jugal Back To Top

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 Back To Top

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 Back To Top

Nice script, thanks!

Monday, September 24, 2012 - 10:44:05 PM - Hassan Parthasarathy Back To Top

Thanks for the nice cmdlet!!


Monday, September 24, 2012 - 10:34:15 AM - Craig A. Silvis Back To Top

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 Back To Top

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 Back To Top

I really liked this posting.

Thank you so much for sharing.


Monday, September 24, 2012 - 8:48:44 AM - Rony Back To Top




Monday, September 24, 2012 - 7:37:48 AM - Darek Back To Top

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.

Learn more about SQL Server tools