SQL Server Utilities - Improve performance of files purge with PowerShell

Here is a technique to improve performance when you come across the task of deleting thousands of files located under many folders

This is a common way to get all the files meeting a criteria of “older than a specific date” from a specific folder including any sub folder if exists while filtering on a specific file extension. The file extensions can be modified or changed to *.* in order to work on all file extensions. The –recurse option is required to work on subfolders and for the file extension filtering.

   1: $days = 15;
   2: $files = Get-ChildItem $path  -recurse -include *.bak | Where-Object {$_.LastWriteTime -lt (Get-Date).AddDays(-$days) -and $_.PSIsContainer -eq $False} 

While the above command is good for most cases it will perform poor in cases where the destination path has many folders with many files.

I came across a case where it took that command around 45 minutes just to complete and populate the $files variable before getting to the actual purge phase.

This is the option I ended up with after various tries and different approaches.

Its performed so much faster in may case where I had a file system with over 800,000 files to deal with and a complex directory structure.

The trick is using the dir command within a cmd window (“C:\Windows\System32\cmd.exe”) and the appropriate switches.

   1: $folders = cmd.exe /c "dir E:\LPR_pic /s /b /ad";

The complete script

#Set-ExecutionPolicy RemoteSigned
  
<#
    20160520 - Yaniv Etrogi 
    Purge files
  
    Paramters:
        $path - The root path we work on recursevly
        $delete_only_archived_files - 1 specifies that only files with the Archive attribute on will be purged
                                      0 specifies that all files will be purged (disregard Archive attribute)
        $days - The number of retention days. Only files older than the (current date - $days) will be purged
                Note that this parameter is assigned multipple times
        $do_delete - 1 specifies that purge takes place 
                     0 specifies only printing (no purge)
#>
  
Set-Location C:
$Attribute = [io.fileattributes]::Archive
$UserInteractive = [Environment]::UserInteractive
$delete_only_archived_files = 1
$do_delete = 1
$path = "E:\LPR_pic\"
$days = 120
  
  
#$folders = Get-ChildItem $path -Recurse | ?{ $_.PSIsContainer } |Select-Object -ExpandProperty FullName
#$folders = Get-ChildItem $path -Recurse | WHERE { $_.PSIsContainer -match"Directory"} |Select-Object -ExpandProperty FullName
  
# Populate an array with folders only
$folders = cmd.exe /c "dir E:\LPR_pic /s /b /ad";
  
  
# Loop over the folders array
foreach($folder in $folders)
{       
    # If the folder is empty delete it
    if ( (Get-ChildItem $folder | Measure-Object).Count -eq 0)
    {
        if ($UserInteractive -eq "True" ) {Write-Host 'Deleting folder...' $folder -ForegroundColor Red}
        Remove-Item -force -Recurse $folder;
    }
    else
    {
        if ($UserInteractive -eq "True" ) {Write-Host 'Processing folder...' $folder -ForegroundColor Green}
        $files = Get-ChildItem $folder | Where-Object {$_.LastWriteTime -lt (Get-Date).AddDays(-$days) -and $_.PSIsContainer -eq $False} 
        
        # Loop over the files in each folder
        foreach($file in $files)
        {                
            if ($do_delete -eq 1)
            {   
                if ($UserInteractive -eq "True" ) {Write-Host -ForegroundColor White $file.FullName }                                         
                $file.Delete();                
            }
            else
            {
                if ($UserInteractive -eq "True" ) {Write-Host -ForegroundColor Yellow $file.FullName }                            
            }
        } 
    
    }   
}
  
  