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
    }

score 7 · Answer 1 · answered Mar 18 '14 at 17:32

7

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

answered Mar 18 '14 at 17:32

Arluin

594
1
8
21

For paths with spaces this works: `"C:\Users\me\Downloads\handle.exe ""$targetfile"""` – Ste May 03 '21 at 15:47
Also if the `handle.exe` is in a folder with spaces `"""C:\Users\me\Downloads New\handle.exe"" ""$targetfile"""` – Ste May 03 '21 at 15:54
The last one was wrong: `& "C:\Users\me\Downloads New\handle.exe" "$targetfile"` This works for spaces in both the `handle.exe` and the `$targetfile`. – Ste May 03 '21 at 16:03
To accept the EULA via command line, use `Invoke-Expression "C:\Users\me\Downloads\handle.exe ""$targetfile"" /accepteula"` – kevinpo Nov 22 '22 at 21:46

score 3 · Answer 2 · answered Feb 08 '12 at 22:35

3

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
}

answered Feb 08 '12 at 22:35

xcud

14,422
3
33
29

I'm actually getting the error 3 times. The first is the complete message as indicated in the title. I then get it 2 more times; but with the file name and path inserted between the words "file" and "because". I'm fairly certain that the last 2 are coming from the Copy and Move statements. But not quite sure where the first is coming from yet....more testing to follow. If I can catch it here; then this would work. – chrisnre Feb 09 '12 at 15:25
1

Some strategic try/catching may be helpful. E.g. http://stackoverflow.com/questions/2182666/powershell-2-0-try-catch-how-to-access-the-exception – xcud Feb 09 '12 at 16:08

score 2 · Answer 3 · answered May 03 '21 at 16:08

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

Andy Arismendi · Answer 4 · 2012-02-09T06:59:48.420

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

Does the "Created" Event fire when the file is initially created or when it is done being created. For example, when I copy a file into a new location the file starts out as 0kb, and then grows to it's full size. Will the event be fired when it's at 0kb, or when it's done, or does the file system hide all this stuff from us? — Localghost, Feb 08 '12 at 21:56
Btw, there is PS module that implements this https://github.com/jfromaniello/pswatch/ — Mike Chaliy, Feb 08 '12 at 23:10
@Jim I updated it so it knows when the file has been fully written. — Andy Arismendi, Feb 08 '12 at 23:59

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

4 Answers4