Log Shipping Issue / Operating system error 32 (The process cannot access the file because it is being used by another process)

“Operating system error 32 (The process cannot access the file because it is being used by another process)” message in SQL Server error log

BackupDiskFile::OpenMedia: Backup device ‘\\NAS\Shared_LogShip\DB_log.trn’ failed to open. Operating system error 32(The process cannot access the file because it is being used by another process.).

If you are using shared NAS for your logs backup location for the primary server and you restoring those logs directly to the secondary server (without copy job) you can face this kind of issues.  If backup and restore job are scheduled at the same time this problem can easily happen. As well can be easily solved.

This happens when the new backup job has been executed and it still running, and the restore job is trying to restore all TRN files it can find. The restore job can handle more than one TRN file, when finished with restoring the first log it will start with the next one (not completed yet, the backup log is still running).

Example

Backup LS primary server

Date 10/12/2017 9:00:00 AM

Log Job History (LSBackup_db)

Step ID 1

Server PrimarySQLServer

Job Name LSBackup_DB

Step Name Log shipping backup log job step.

Duration 00:00:06

Message

2017-10-12 09:00:06.02 Deleting old log backup files. Primary Database: ‘DB’

2017-10-12 09:00:06.08 The backup operation was successful. Primary Database: ‘DB’, Log Backup File: ‘\\NAS\Shared_LogShip\db_20171011200000.trn’ 

2017-10-12 09:00:06.09 —– END OF TRANSACTION LOG BACKUP   —–

Restore LS secondary server

Successfully restored log db_20171011194501.trn

Date 10/12/2017 9:00:00 AM

Log Job History (LSRestore_DB)

Step ID 1

Server SecondarySQLServer

Job Name LSRestore_DB

Step Name Log shipping restore log job step.

Duration 00:00:01

Message

2017-10-12 09:00:01.00 Starting transaction log restore. Secondary ID: ‘cac4152a-3598-4608-bf27-cd8daf45213b’

2017-10-12 09:00:01.00 Retrieving restore settings. Secondary ID: ‘cac4152a-3598-4608-bf27-cd8daf45213b’

2017-10-12 09:00:01.02 Retrieved common restore settings. Primary Server: ‘PrimarySQLServer’, Primary Database: ‘DB’, Backup Destination Directory: ‘\\NAS\Shared_LogShip\’, File Retention Period: 4320 minute(s)

2017-10-12 09:00:01.02 Retrieved database restore settings. Secondary Database: ‘DB’, Restore Delay: 0, Restore All: True, Restore Mode: Standby,  Last Restored File: \\NAS\Shared_LogShip\db_20171011194501.trn, Block Size: Not Specified, Buffer Count: Not Specified, Max Transfer Size: Not Specified

And failing to restore new log db_20171011200000.trn

Date 10/12/2017 9:00:00 AM

Log Job History (LSRestore_DB)

Step ID 1

Server SecondarySQLServer

Job Name LSRestore_DB

Step Name Log shipping restore log job step.

Duration 00:00:03

Message

2017-10-12 09:00:03.03 *** Error: Could not apply log backup file ‘\\NAS\Shared_LogShip\db_20171011200000.trn’ to secondary database ‘db’.(Microsoft.SqlServer.Management.LogShipping) ***

2017-10-12 09:00:03.03 *** Error: Cannot open backup device ‘\\NAS\Shared_LogShip\db_20171011200000.trn’. Operating system error 32(The process cannot access the file because it is being used by another process.).RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***

2017-10-12 09:00:03.05 Skipping log backup file ‘\\NAS\Shared_LogShip\db_20171011200000.trn’ for secondary database ‘db’ because the file could not be verified.

2017-10-12 09:00:03.05 Number of log backup files restored: 0

So what happened here is:

LSBackup job on the primary server created a new log with log name db_20171011200000.trn, at the same time LSRestore job on the secondary server Successfully restores previous log file  db_20171011194501.trn, and starts restoring new one which is still in use by LSBackup job on the primary. You can see that backup log job on primary took 6 sec to finish, and restore job on secondary server restored the first log within 3 seconds and starts with restoring next one which was still in use by the LSBackup job.

You can easily fix this with just putting the restore delay within transaction log shipping configuration

So next time LSRestore job on secondary server will wait with restoring logs for the delay time you put in log shipping configuration. In this example, this will be more than enough time for the LSBackup job on the primary server to finish current log backup process.

Similar Posts:

• SQL Server / Upgrading the Secondary Server Instances (STANDBY)
• SQL Server / SQL Server Management Studio could not delete the Secondary server / Secondary not available
• SQL Server / Initialize Secondary Database / Generate a full backup of the primary database using SSMS transaction log shipping GUI issue
• PowerShell / SQL Server / Generate SQL Script to Restore Multiple Transaction Logs
• SQL Server / Monitor Backup and Restore progress