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

Question

Below is a script that tracks the directory and its subfolders for stored files. About every 10 minutes I search for new files, and then compare them with the database table, which indicates where to transfer them, then copies the files to the local archive, moves them to the places that need to be moved and inserts the record into another database table data with file attributes, as well as where it came and went. If there are no matches in the database - or there is a script error - he sends me an email.

However, since the files are constantly placed in the directory, it is possible that the file is still being written during script execution. As a result, I get the error The process cannot access the file because it is being used by another process. email me all the time. Also, because I do not deal with the error in advance; it goes through a cycle, and a false record with invalid file attributes is inserted into my log table in the database. When the file is finally freed, it is inserted again.

I am looking for a way to identify the files to which processes are attached; and skip them when running the script - but several days of searching the Internet and some testing have not yet given an answer.

 ## 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 <[e-mail]>" $email_recip = [STRING]"[e-mail]" $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

Alternatively, you can check for errors either with try / catch, or by looking for the $ errors collection after trying Move-Item, and then handle this condition accordingly.

 $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 } 

Answer 2

You can use SysInternals handles.exe to find open file descriptors. 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 a 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 is created in a folder that you control, such as a new file.

To run the code when the file copy is complete, you will need to listen to the changed event. In this case, there is a slight problem with the fact that it starts once when the file starts to copy and again when it is completed. I had the idea to get around this chicken / egg problem after you checked Mike's comment module. I updated the code below so that it only runs the code when the file is completely written.

To try it, change $folderToMonitor to the folder you want to control and add 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