How do I remove a file from a filegroup?

Question

question

Matt Whitfield asked Oct 28, '09

How do I remove a file from a filegroup?

Seeder question: I have a file in a filegroup which was created in the wrong place, so I need to remove it and add it back in the right place. However, SQL Server tells me that I cannot remove the file because it has data in it. I cannot take the database off-line completely because it is in constant use. How do I go about removing and adding this file in the right place?

t-sql filegroup storage

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2009-10-28T10:09:05Z

Kev Riley answered Oct 28, '09

You need to run DBCC SHRINKFILE with the EMPTYFILE clause, to move data from the file into other files in the same filegroup.

SQL will then prevent any more data being written to that file. You will then be able to use ALTER DATABASE to remove that file.

Alternatively, you can do this from SSMS. This MSDN article takes you through the steps

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question