olahallengren
AG Group Backups Log Failures - File being used by another process.
Description of the issue I'm using multi-server administration to manage my backup in my environment. Recently, we added a 2 server DB availability group to that environment, and deployed the jobs as I have before (e.g. deploy Ola code to new server, but don't create jobs, then use MSX server to deploy jobs).
However, after the jobs started running, the log backups would fail about once an hour on a job running every 15 minutes. In looking at the errors, I'm seeing the following:
Msg 3201, Sev 16, State 1, Line 1 : Cannot open backup device '\....\SQLBackups.....\ApplicationLogging_LOG_20200520_160001.trn'. Operating system error 32(The process cannot access the file because it is being used by another process.). [SQLSTATE 42000]
Spent a lot of time and effort with my Operations team, thinking the issue was on the backup device, and didn't find anything. On a whim, I started looking at the backup history is the MSDB database and noticed that it looks like the log backup jobs were trying to do backups on both servers at the same time. I see an entry for the database on each server, at approximately the same time (milliseconds difference). Yet, where the failures were, there was only a success entry on one server, not both. Checking the backup folder, and sure enough, there are 2 log files showing approximately the same timestamp.
Remove the log job from 1 server prevented additional failures. Attempting to restore the database, and I needed logs from both servers to make the backup work, which is worrisome.
What am I doing wrong with the backups?
I am following the advice here: Ola FAQ, under the "How should I configure DatabaseBackup to back up an Availability Group?"
- Both jobs are configured identically and managed by the MSX server
- Backup Preference = Preferred Secondary
- I want non-copy full/diff backups on primary and log backups on secondary, which is how I believe I have things configured.
Is this a bug or have I miscofigured something?
SQL Server version and edition Microsoft SQL Server 2019 (RTM-CU4) (KB4548597) - 15.0.4033.1 (X64) Mar 14 2020 16:10:35 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)
Version of the script Version: 2020-01-26 14:06:53
What command are you executing? EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'\SQLBackups', @BackupType = 'LOG', @Verify = 'Y', @CleanupTime = 336, @CheckSum = 'Y', @LogToTable = 'Y'
What output are you getting? Msg 3201, Sev 16, State 1, Line 1 : Cannot open backup device '\....\SQLBackups.....\ApplicationLogging_LOG_20200520_160001.trn'. Operating system error 32(The process cannot access the file because it is being used by another process.). [SQLSTATE 42000]
Could you send me the full output from dbo.DatabaseBackup from both servers?
Could you run this on both replicas?
SELECT sys.fn_hadr_backup_is_preferred_replica ('DatabaseName')
Could you also try updating to the latest CU? Do you still have the issue?
I was looking at this again. DatabaseBackup is using sys.fn_hadr_backup_is_preferred_replica, to only do the backup on the preferred backup replica. In your case that should be the secondary replica.
I would need to see the output from DatabaseBackup on both replicas, to see where the issue is.