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?

more ▼

asked Oct 28, 2009 at 09:56 AM in Default

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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

more ▼

answered Oct 28, 2009 at 10:09 AM

Kev Riley gravatar image

Kev Riley ♦♦
53.8k 47 49 76

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 28, 2009 at 09:56 AM

Seen: 3529 times

Last Updated: Oct 28, 2009 at 09:56 AM