REMOVE FILE and REMOVE FILEGROUP error message
- Posted by Sqltimes
- On December 24, 2014
- 0 Comments
Quick one today:
A few months ago, this interesting error occurred. As needed new FileGroups were being created and destroyed and that when I ran into this interesting error.
Follow these steps to recreate the issue:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
|
---- First create FG and file--ALTER DATABASE SampleDB ADD FILEGROUP [SV_FG]GOALTER DATABASE SampleDB ADD FILE ( NAME = N'StoredValue_FG' , FILENAME = N'I:\MSSQL\Data\StoredValue_FG.NDF' , SIZE = 100 MB , FILEGROWTH = 10%)TO FILEGROUP [SV_FG]GO---- Drop them both--ALTER DATABASE SampleDBREMOVE FILE StoredValue_FGGOALTER DATABASE SampleDBREMOVE FILEGROUP [SV_FG]GO---- Now, when you recreate the same FG and file, you get this error--ALTER DATABASE SampleDB ADD FILEGROUP [SV_FG]GOALTER DATABASE SampleDB ADD FILE ( NAME = N'StoredValue_FG' , FILENAME = N'I:\MSSQL\Data\StoredValue_FG.NDF' , SIZE = 100 MB , FILEGROWTH = 10%)TO FILEGROUP [SV_FG]GO |
Error Message on a Sql Server 2012 Instance
Msg 1833, Level 16, State 2, Line 1 File 'StoredValue_FG' 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.
Error Messaage on a Sql Server 2008 R2 Instance
Msg 1833, Level 16, State 1, Line 1 File 'I:\MSSQL\Data\StoredValue_FG.NDF' cannot be reused until after the next BACKUP LOG operation. Msg 1833, Level 16, State 2, Line 1 File 'StoredValue_FG' cannot be reused until after the next BACKUP LOG operation.
Solution:
As the error message indicates, take a LOG backup first and then re run the script. New file will be successfully added to the FileGroup.
More importantly, what is the reason for this error? My guess is as we run DML/DDL statements that activity is logged in detail in the transactional log. So when a new data file is created, the transactional log makes a note of it. So, when you create a new data file, Sql Server checks if a data file with same name exists. If it does it throws an error. But in this case, the data file with same name does not exits. But it exists with in the same active transaction log and that is why we see this error.
But once the active log window passes, we do not see this error. More importantly, in SIMPLE recovery mode, this processes of creating, dropping and recreating a data file with the same name does not throw any error. Because, CHECKPOINT occurs and the active log is moves past the section where the old data file creation is noted. The behavior measure so far points to this pattern, but your comments are welcome.
What do you think? Please share your thoughts in comments.
0 Comments