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 <[email protected]>"
$email_recip = [STRING]"[email protected]"
$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

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