We have a three jobs scheduled on production server. Full backup, Differential backup and Transaction Log backup. The first two backup successfully, but the Transaction Log backup is failing. It shows below error.
Date 1/23/2019 4:00:00 AM
Log Job History (JOB_NAME)
Step ID 1
Job Name (JOB_NAME)
Step Name 1
Sql Severity 16
Sql Message ID 3013
Operator Net sent
Retries Attempted 0
Executed as user: NT SERVICE\SQLAgent$MSSQLSERVER_2016. Cannot open backup device 'BACKUP_PATH\database_Trans_4.trn'. Operating system error 32(The process cannot access the file because it is being used by another process.). [SQLSTATE 42000] (Error 3201) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
I have edited the message to hide the server details for security reasons.
The Backup script used is
Declare @number nchar(2) set @number=isnull((select max(ID) from database.dbo.Backup_Transactionlog),0)+1 declare @path1 varchar(max) set @path1=N'E:\DB_Backup\database_TransactionalLog_Backup\database_Trans_' + ltrim(rtrim(@number)) +'.trn' BACKUP LOG [database] TO DISK = @path1 WITH INIT insert into database.dbo.Backup_Transactionlog values (@path1,getdate())
After doing lots of research i found a solution. I changed the WITH INIT Option of the backup syntax with WITH FORMAT. But still the backup is failing.
Could any one please provide any solution that would make my day.