Powershell - "The process cannot access the file because it is being used by another process"

Question

Below is a script that monitors a directory and its subfolders for deposited files. Every 10 minutes or so, I look for new files and then match them against a database table that tell me where they need to be moved to - then it copies the files to a local archive, moves them to the locations they need to be moved to, and inserts a record into another database table with the file's attributes and where it came and went. If there is no match in the database - or there is an script error - it sends me an email.

However, since files are getting deposited to the directory constantly, it's possible that a file is still being written when the script executes. As a result, I get the error The process cannot access the file because it is being used by another process. emailed to me all the time. In addition, because I'm not dealing with the error up front; it goes through the loop and a false entry is inserted into my log table in the database with incorrect file attributes. When the file finally frees up, it gets inserted again.

I'm looking for a way to identify files that have processes attached to them; and skipping them when the script executes - but several days of web searches and some testing hasn't yielded an answer yet.

## CLEAR ERROR LOG
$error.clear()

Write-Host "***File Transfer Script***"

## PARAMETERS
$source_path = "D:\Files\In\"
$xferfail_path = "D:\Files\XferFailed\"
$archive_path = "D:\Files\XferArchive\"
$email_from = "SQLMail <SQLMail@bar.com>"
$email_recip = [STRING]"foo@bar.com"
$smtp_server = "email.bar.com"
$secpasswd = ConvertTo-SecureString "Pa$$w0rd" -AsPlainText -Force
$smtp_cred = New-Object System.Management.Automation.PSCredential ("BAR\SQLAdmin", $secpasswd)

## SQL LOG FUNCTION
function Run-SQL ([string]$filename, [string]$filepath, [int]$filesize, [int]$rowcount, [string]$xferpath)
    {
        $date = get-date -format G
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = "Server=SQLSERVER;Database=DATABASE;Uid=SQLAdmin;Pwd=Pa$$w0rd;"
        $SqlConnection.Open()
        $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
        $SqlCmd.CommandText = "INSERT INTO DATABASE..Table VALUES ('$date','$filename','$filepath',$filesize,$rowcount,'$xferpath',0)"
        $SqlCmd.Connection = $SqlConnection
        $SqlCmd.ExecuteNonQuery()
        $SqlConnection.Close()
    }


## DETERMINE IF THERE ARE ANY FILES TO PROCESS
$file_count = Get-ChildItem -path $source_path |? {$_.PSIsContainer} `
              | Get-ChildItem -path {$_.FullName} -Recurse | Where {$_.psIsContainer -eq $false} | Where {$_.Fullname -notlike "D:\Files\In\MCI\*"} `
              | Measure-Object | Select Count

If ($file_count.Count -gt 0)
    {
        Write-Host $file_count.Count "File(s) Found - Processing."
        Start-Sleep -s 5


    ## CREATE LIST OF DIRECTORIES
    $dirs = Get-ChildItem -path $source_path -Recurse | Where {$_.psIsContainer -eq $true} | Where {$_.Fullname -ne "D:\Files\In\MCI"} `
                                                      | Where {$_.Fullname -notlike "D:\Files\In\MCI\*"}


    ## CREATE LIST OF FILES IN ALL DIRECTORIES
    $files = ForEach ($item in $dirs)     
        {
            Get-ChildItem -path $item.FullName | Where {$_.psIsContainer -eq $false} | Sort-Object -Property lastWriteTime -Descending
        }


    ## START LOOPING THROUGH FILE LIST
    ForEach ($item in $files)
        {
            ## QUERY DATABASE FOR FILENAME MATCH, AND RETURN TRANSFER DIRECTORY
            $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
            $SqlConnection.ConnectionString = "Server=SQLSERVER;Database=DATABASE;Uid=SQLAdmin;Pwd=Pa$$w0rd;"
            $SqlConnection.Open()
            $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
            $SqlCmd.CommandText = "SELECT F.DirTransfer FROM DATABASE..Files F WHERE '$item.Name.Trim()' LIKE F.FileName"
            $SqlCmd.Connection = $SqlConnection
            $DirTransfer = $SqlCmd.ExecuteScalar()
            $SqlConnection.Close()

            If ($DirTransfer) # if there is a match
                {
                    Write-Host $item.FullName"`t->`t"$DirTransfer
                    $filename = $item.Name
                    $filepath = $item.FullName
                    $filesize = $item.Length
                        If (!($filesize))
                            {
                                $filesize = 0
                            }
                    $rowcount = (Get-Content -Path $item.FullName).Length
                        If (!($rowcount))
                            {
                                $rowcount = 0
                            }
                    $xferpath = $DirTransfer
                    Run-SQL -filename "$filename" -filepath "$filepath" -filesize "$filesize" -rowcount "$rowcount" -xferpath "$DirTransfer"
                    Copy-Item -path $item.FullName -destination $DirTransfer -force -erroraction "silentlycontinue"
                    Move-Item -path $item.FullName -destination $archive_path -force -erroraction "silentlycontinue"
                    #Write-Host "$filename   $filepath   $filesize    $rowcount   $xferpath"

                }
            Else # if there is no match
                {
                    Write-Host $item.FullName "does not have a mapping"
                    Move-Item -path $item.FullName -destination $xferfail_path -force
                    $filename = $item.FullName
                    $email_body = "$filename `r`n`r`n does not have a file transfer mapping setup"
                    Send-MailMessage -To $email_recip `
                                     -From $email_from `
                                     -SmtpServer $smtp_server `
                                     -Subject "File Transfer Error - $item" `
                                     -Body $email_body `
                                     -Priority "High" `
                                     -Credential $smtp_cred
                }
        }



}
## IF NO FILES, THEN CLOSE
Else
{
    Write-Host "No File(s) Found - Aborting."
    Start-Sleep -s 5
}

## SEND EMAIL NOTIFICATION IF SCRIPT ERROR

If ($error.count -gt 0)
    {
        $email_body = "$error"
        Send-MailMessage -To $email_recip `
                         -From $email_from `
                         -SmtpServer $smtp_server `
                         -Subject "File Transfer Error - Script" `
                         -Body $email_body `
                         -Priority "High" `
                         -Credential $smtp_cred
    }

Answer 1

To expand on Arluin's answer. It fails if there's spaces in either the handle.exe or the $targetfile.

This will work for spaces in both and also formats the result to give you the Program Name.exe

$targetfile = "W:\Apps Folder\File.json"
$result = & "W:\Apps (Portable)\handle.exe" "$targetfile" | Select-String ([System.IO.Path]::GetFileNameWithoutExtension($targetfile))
$result = $result -replace '\s+pid\:.+'
$result
# PS> FreeCommander.exe

Answer 2

You can use the SysInternals handles.exe to find the open handles on a file. The exe can be downloaded from http://live.sysinternals.com/.

$targetfile = "C:\Users\me\Downloads\The-DSC-Book.docx"
$result = Invoke-Expression "C:\Users\me\Downloads\handle.exe $targetfile" | Select-String ([System.IO.Path]::GetFileNameWithoutExtension($targetfile))
$result

Outputs:

WINWORD.EXE        pid: 3744   type: File           1A0: C:\Users\me\Downloads\The-DSC-Book.docx

Answer 3

One way to avoid file locks caused by running the script on a timer is to use an event driven approach using a file system watcher. It has the ability to execute code when an event such as a new file is created in the folder you are monitoring.

To run code when the file is finished copying you would need to listen for the changed event. There is a slight issue with this event in that it fires once when the file begins copying and again when it is finished. I got an idea to work around this chicken/egg problem after checking out the module Mike linked to in the comments. I've updated the code below so that it will only fire off code when file has fully been written.

To try, change $folderToMonitor to the folder you want to monitor and add some code to process the file.

$processFile = {    
    try {
        $filePath = $event.sourceEventArgs.FullPath
        [IO.File]::OpenRead($filePath).Close()

        #A Way to prevent false positive for really small files.
        if (-not ($newFiles -contains $filePath)) {
            $newFiles += $filePath

            #Process $filePath here...
        }
    } catch {
        #File is still being created, we wait till next event.
    }   
}

$folderToMonitor = 'C:\Folder_To_Monitor'

$watcher = New-Object System.IO.FileSystemWatcher -Property @{
    Path = $folderToMonitor
    Filter = $null
    IncludeSubdirectories = $true
    EnableRaisingEvents = $true
    NotifyFilter = [System.IO.NotifyFilters]'FileName,LastWrite'
}

$script:newFiles = @()
Register-ObjectEvent $watcher -EventName Changed -Action $processFile > $null

Answer 4

Alternatively, you can check for errors either via try/catch or by looking at the $error collection after the Move-Item attempt then handle the condition appropriately.

$error.Clear()
Move-Item -path $item.FullName -destination $xferfail_path -force -ea 0
if($error.Count -eq 0) {
  # do something useful
}
else {
  # do something that doesn't involve spamming oneself
}