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

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(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

avatar image

Kev Riley ♦♦
66.8k 48 65 81

(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.

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: 7357 times

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

Copyright 2018 Redgate Software. Privacy Policy