Sql Server Error Message: File ‘filename’ cannot be reused until after the next BACKUP LOG operation. If the database is participating in an availability group, a dropped file can be reused only after the truncation LSN of the primary availability replica has passed the drop LSN of the file and a subsequent BACKUP LOG operation has completed.
- Posted by Sqltimes
- On June 14, 2014
- 0 Comments
Quick one today:
Again today, I ran into another interesting error message.
In our lab environment, playing with one of our customer databases, I ran into this interesting error.
Msg 1833, Level 16, State 2, Line 5 File 'filename' cannot be reused until after the next BACKUP LOG operation. If the database is participating in an availability group, a dropped file can be reused only after the truncation LSN of the primary availability replica has passed the drop LSN of the file and a subsequent BACKUP LOG operation has completed.
Essentially, it is saying:
- The same file name (ndf file) cannot be reused until BACKUP LOG is performed
- If you have Availability Groups configured, same file name cannot be re-created, until the “remove file” change has successfully synchronized to the replicas.
This is how I ran into this issue:
Step 1: Create a new FG and File
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
---- Create a filegroup--ALTER DATABASE SampleDBADD FILEGROUP [SV_FG]GO---- Add a data file to it--ALTER DATABASE SampleDBADD FILE ( NAME = N'NewDataFile' , FILENAME = N'I:\MSSQL\Data\NewDataFile.NDF' , SIZE = 2 GB , FILEGROWTH= 10%)TO FILEGROUP [SV_FG]GO |
Step 2: DROP the FG and data file
|
1
2
3
4
5
6
7
8
9
10
|
---- Now drop the file and its associated data file--ALTER DATABASE SampleDBREMOVE FILE NewDataFileGOALTER DATABASE SampleDBREMOVE FILEGROUP [SV_FG]GO |
Step 3: Recreate same FG and data file
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
---- Now re-create the same filegroup and file.-- FG runs successfully, but when you create the file, it throws an error message------ Create a filegroup--ALTER DATABASE SampleDBADD FILEGROUP [SV_FG]GO---- Add a data file to it--ALTER DATABASE SampleDBADD FILE (NAME = N'NewDataFile', FILENAME = N'I:\MSSQL\Data\NewDataFile.NDF', SIZE = 2 GB, FILEGROWTH= 10%)TO FILEGROUP [SV_FG]GO |
Resolution
Creating FG runs successfully, but creating the data file throws an error. Following the instructions in the error message, once I performed transactional log backup, a new data file (.ndf) was successfully created with the same old name.
|
1
2
3
4
5
6
|
---- Perform transactionallog backup--BACKUP LOG SampleDBTO DISK = N'M:\MSSQL\Backup\MSSQLSERVER\SampleDB.TRN'GO |
Now, when the same script to create new filegroup and file are executed, it runs successfully.
|
1
2
3
4
5
6
7
8
9
10
11
12
|
---- Add a data file to it--ALTER DATABASE SampleDBADD FILE ( NAME = N'NewDataFile' , FILENAME = N'I:\MSSQL\Data\NewDataFile.NDF' , SIZE = 2 GB , FILEGROWTH= 10%)TO FILEGROUP [SV_FG]GO |
Conclusions
So, why does Sql Server care that we take a transactional backup when a data file is dropped and being re-created. My guess is:
- The “REMOVE FILE” change must be synchronized to the replicas before a new file with same name is created.
- Sql Server wants to secure the changes. Since file level changes are not the same as table or function or other objects, may be Sql Server want to secure these into a safe backup file, then move on to making more changes. If you have a better or correct explanation, please share in the comments.
Hope this helps,
_Sqltimes
0 Comments