SQL SERVER – Msg 1833 – File Cannot be Reused Until After the Next BACKUP LOG Operation

While preparing for a demo for an upcoming session, I came across an error which I didn’t see earlier. Here is the error in the UI and it is related to Backup Log Operation.

SQL SERVER - Msg 1833 - File Cannot be Reused Until After the Next BACKUP LOG Operation file-add-error

I am also trying to add a file to the database using below command.

Solarwinds
USE [master]
GO
ALTER DATABASE [SQLAuth] ADD FILE ( NAME = N'SQLAuth_LogNew', FILENAME = N'F:\DATA\SQLAuth_LogNew.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [PRIMARY]
GO

And it also failed with following error.

If I change the file name, it works. As mentioned in error message I performed a log backup, and I was able to add the file again.

ROOT CAUSE

While working yesterday, I already added the file and dropped it. As per error message, if I need to reuse the same logical name of the database file, I must take a log backup. Here is the simple reproduction of the error.

CREATE DATABASE [DropFileTest]
GO
USE [master]
GO
-- take a full backup to make it real full else it would act as simple.
BACKUP DATABASE [DropFileTest] TO  DISK = N'DropFileTest.bak' 
GO
-- add a new LDF file
USE [master]
GO
ALTER DATABASE [DropFileTest] ADD LOG FILE ( NAME = N'NewLDF', 
FILENAME = N'F:\LOG\NewLDF.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO
-- reomve the file.
USE [DropFileTest]
GO
ALTER DATABASE [DropFileTest]  REMOVE FILE [NewLDF]
GO
-- Adding same file again and it should fail.
USE [master]
GO
ALTER DATABASE [DropFileTest] ADD LOG FILE ( NAME = N'NewLDF', 
FILENAME = N'F:\LOG\NewLDF.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO

If you run above, you can notice the same error.

WORKAROUND/FIX

As mentioned in the error message, take a transaction log backup of the database. In the above demo script, we can do below before adding a file and it should work.

BACKUP LOG [DropFileTest] TO  DISK = N'DropFileTest.trn' 
GO
-- Adding same file again and it should work this time.
USE [master]
GO
ALTER DATABASE [DropFileTest] ADD LOG FILE ( NAME = N'NewLDF', FILENAME = N'F:\LOG\NewLDF.ldf' )
GO

Have you seen such interesting error?

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , , ,
Previous Post
SQL SERVER – FIX: Msg 35250 – The Connection to the Primary Replica is Not Active. The Command Cannot be Processed
Next Post
SQL SERVER – FIX: RequireKerberos Error During SQL 2008 Installation on Windows 2008 R2 Cluster

Related Posts

1 Comment. Leave new

  • Chandan Gupta
    July 27, 2017 8:45 pm

    Hi,
    Cannot Understand, How the TRN backup have resolved the problem, I mean to ask how it internally works, what Principle is used to resolved it. ?

    Reply

Leave a Reply

Menu