Troubleshooting File in Use Problem Preventing Validated Sync from Proceeding on SQL Server dSources (KBA6137)

Last updated
Save as PDF
Share
1. Share
2. Tweet
3. Share

KBA

KBA# 6137

Applicable Delphix Versions

Click here to view the versions of the Delphix engine to which this article applies

Major Release	All Sub Releases
6.0	6.0.0.0, 6.0.1.0, 6.0.1.1, 6.0.2.0, 6.0.2.1, 6.0.3.0, 6.0.3.1, 6.0.4.0, 6.0.4.1, 6.0.4.2. 6.0.5.0
5.3	5.3.0.0, 5.3.0.1, 5.3.0.2, 5.3.0.3, 5.3.1.0, 5.3.1.1, 5.3.1.2, 5.3.2.0, 5.3.3.0, 5.3.3.1, 5.3.4.0, 5.3.5.0 5.3.6.0, 5.3.7.0, 5.3.7.1, 5.3.8.0, 5.3.8.1, 5.3.9.0
5.2	5.2.2.0, 5.2.2.1, 5.2.3.0, 5.2.4.0, 5.2.5.0, 5.2.5.1, 5.2.6.0, 5.2.6.1
5.1	5.1.0.0, 5.1.1.0, 5.1.2.0, 5.1.3.0, 5.1.4.0, 5.1.5.0, 5.1.5.1, 5.1.6.0, 5.1.7.0, 5.1.8.0, 5.1.8.1, 5.1.9.0, 5.1.10.0
5.0	5.0.1.0, 5.0.1.1, 5.0.2.0, 5.0.2.1, 5.0.2.2, 5.0.2.3, 5.0.3.0, 5.0.3.1, 5.0.4.0, 5.0.4.1 ,5.0.5.0, 5.0.5.1, 5.0.5.2, 5.0.5.3, 5.0.5.4
4.3	4.3.1.0, 4.3.2.0, 4.3.2.1, 4.3.3.0, 4.3.4.0, 4.3.4.1, 4.3.5.0
4.2	4.2.0.0, 4.2.0.3, 4.2.1.0, 4.2.1.1, 4.2.2.0, 4.2.2.1, 4.2.3.0, 4.2.4.0 , 4.2.5.0, 4.2.5.1
4.1	4.1.0.0, 4.1.2.0, 4.1.3.0, 4.1.3.1, 4.1.3.2, 4.1.4.0, 4.1.5.0, 4.1.6.0

Validated sync fails due to file in use error

Sometimes a SQLCMD script used for restoring a SQL Server dSource hangs and stalls further dSource synchronizations. This can be due to a variety of reasons, but mostly stems from a disruption in the processing of the backup file, either when reading the backup header or actually restoring it.

You can detect this scenario by checking the critical fault that occurs in this scenario:

CRITICAL
Validated sync for the dSource failed
Sep 28, 2020 4:45 PM


Validated sync for dSource "Ipa" failed with the error: Failed to push script to the remote host "10.43.18.97". Command output: java.io.FileNotFoundException: E:\Delphix\DelphixConnector\564d2b04-403a-5351-ea1c-3726ff0861e1-staging-1\SCRIPT\RestoreBackupHeader.sql (The process cannot access the file because it is being used by another process)

User Action:  Review the error. Additional recommendation(s): Make sure that the host is up, the Delphix Connector service is running, the OS credentials for user "dtully" are correct, the user has the required permissions, the drive where Delphix Connector is installed has sufficient free space and try the operation again..

In this specific example, the file in use is RestoreBackupHeader.sql. In a more realistic scenario, one of the actual restore files might be in use such as these files:

RestoreStagingDBNoRecovery.sql
RestoreSeedDBWithNoRecovery.sql

In any event, the main issue here is the SQLCMD process has a hold on a SQL script and Delphix is unable to proceed past this point.

How to resolve the file in use situation

Here are the steps to remove the hung process from the Windows staging host that is blocking the validated sync thread from proceeding:

You know the file is a SQL script handled by the SQLCMD process, which is a SQL Server utility commonly used to process input SQL via text file. An effective way to identify the process holding this file is using Resource Monitor on the Windows staging host.
Under Resource Monitor select the CPU tab.
Under the Processes list look for SQLCMD.exe. You can check them all if there is more than one.
Open the Associated Handles section and filter by the file name you are troubleshooting. You will want to match the file name from your critical fault message with the file name specified by the SQLCMD process as seen in this image:

5. Here we can match this file with the message text. Take note of the PID (Process ID) as you will use it in the next step to kill the process.

6. From a powershell console or command prompt you can run these commands to remove the process. This ensures you are targeting the correct process.

PS E:\> tasklist | findstr "6252"
SQLCMD.EXE                    6252 RDP-Tcp#4                  2     12,500 K

PS E:\> taskkill /F /PID 6252
SUCCESS: The process with PID 6252 has been terminated.
PS E:\>

7. After the termination, wait a few minutes and check the status of the dSource to check if validated sync has resumed:

8. When you see the "Restoring source database..." status the validated sync has resumed. If for some reason the backup files are no longer available, you will need to manually sync to a Full backup to bring the staging database up-to-date.