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

By: Jugal Shah   |   Read Comments (11)   |   Related Tips: More > PowerShell

Problem

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.

Solution

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

Syntax

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

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.

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

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

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

 Get-ChildItem d:\backup -recurse

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*"}

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
$largeSizefiles

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.

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 Step

• 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

About the author

Jugal has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2.

View all my tips

We Recommend

• More SQL Server DBA Tips...

Print

Tweet

Become a paid author

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code