2

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

Server SERVER_NAME

Job Name (JOB_NAME)

Step Name 1

Duration 00:00:01

Sql Severity 16

Sql Message ID 3013

Operator Emailed

Operator Net sent

Operator Paged

Retries Attempted 0

Message

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.

Thanks.!!

  • Try to change backup file name with smth else, for example 'tran" + select replace(replace(convert(char(19), getdate(), 120), ':', ''), ' ','') – sepupic Jan 23 at 12:56
  • There is one strange thing that is happening. This is scheduled backup so the backup is taken every 1 hr. But one of the backup is successful. for eg. if the backup is taken in the following sequence 1ST BACKUP AT 12 AM FAILED; 2ND BACKUP AT 1 AM FAILED; 3RD BACKUP AT 2 AM FAILED; 4TH BACKUP AT 3 AM FAILED; 5TH BACKUP AT 4 AM SUCCESS How can this happen? – Karan.T. Jan 23 at 12:58
  • 1
    @karanT probably since the 5th backup is not being used by a process, or it is a new .trn file. The processes that could be using it are restores, other backups to that file, antivirus scans , ... . Do you need to backup to the same files? I would advise looking into ola hallengren's backup solution. – Randi Vertongen Jan 23 at 13:09
  • @RandiVertongen every time a new backup .trn files is created. It will overwrite the previous .trn files in the folder. How do I come to know, which process is blocking the backup? – Karan.T. Jan 23 at 13:49
  • 2
    I have to ask, why are you overwriting tlogs? Also, why log the execution? It's logged by default in msdb.dbo.backupset (and subsequent tables). – scsimon Jan 23 at 14:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.