source: http://www.MSSQLTips.com/tip.asp?id=2774 -- printed: 9/3/2015 12:27:54 AM

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

Written By: Jugal Shah -- 9/24/2012

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.

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

GCI Command INCLUDE SELECT

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

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
 

Follow

Get Free SQL Tips

Twitter

LinkedIn

Google+

Facebook

Pinterest

RSS

Learning

DBAs

Developers

BI Professionals

Careers

Q and A

Today's Tip

Resources

Tutorials

Webcasts

Whitepapers

Tools

Search

Tip Categories

Search By TipID

Authors

Community

First Timer?

Pictures

Free T-shirt

Contribute

Events

User Groups

Author of the Year

More Info

Join

About

Copyright

Privacy

Disclaimer

Feedback

Advertise

Copyright (c) 2006-2015 Edgewood Solutions, LLC All rights reserved
Some names and products listed are the registered trademarks of their respective owners.