2

Hope someone can help me on this one.

I have created by mistake an additional data file in the incorrect file group, I need to either remove the file completely or move the file to the default file group. Looks like changing files from one file group to another is not possible?

The data file is empty so I tried removing the file using alter DB remove file, when I run DBCC showfilestats, the file is gone, but when I check SYS.master_files, I can see still the entry of the file I want removed. Is there a way to remove it completely, even deleting it physically from the server as I need to recover the space?

Kind regards, Jose.

  • 1
    If the file is not listed in sys.database_files, it can be deleted safely. – Dan Guzman Feb 4 '19 at 23:30
  • All good now, was not listed by sys.database_files and finally was able to remove it from the server. Many thanks @DanGuzman – Jose Espinoza Feb 4 '19 at 23:46
2

Looks like changing files from one file group to another is not possible?

Correct, you would need to create a new file on the correct filegroup and recreate all your objects on the correct filegroup (if any existed) to move them, as you cannot move a file between filegroups.

The data file is empty so I tried removing the file using alter DB remove file, when I run DBCC showfilestats, the file is gone, but when I check SYS.master_files, I can see still the entry of the file I want removed. Is there a way to remove it completely, even deleting it physically from the server as I need to recover the space?

I've seen this before in SQL Server 2012. If you query sys.master_files you will see the drop_lsn field has an LSN value in it for this file. This indicates the LSN at which this file was dropped. You need to perform a log backup to clear this value, then it will disappear from the DMVs.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.