Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips














































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

MSSQLTips author Jugal Shah By:   |   Read Comments (12)   |   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.

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


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




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
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.


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

GOOD

 

 


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 - 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 - 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 - 10:44:05 PM - Hassan Parthasarathy Read The Tip

Thanks for the nice cmdlet!!

Partha


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

Nice script, thanks!


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


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.


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

Cool. LikeD it. Thanks a lot for sharing.


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


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

 



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
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.