Remove a File and Filegroup from Database

Before a filegroup can be removed all files associated with it need to be deleted.

Before a file can be deleted all objects associated with it need to be removed.

Just removing a file not a filegroup

If you have multiple files on a filegroup and you just want to get rid of one you can run this command.

DBCC ShrinkFile(logical_file_name,EmptyFile);

This will move any objects in the file onto the other files in the filegroup. You can then delete the file.

ALTER DATABASE DatabaseName REMOVE FILE logical_file_name;

Removing a file and a filegroup

To do this you will have to make sure that here are no objects associated with the file group. This could be Tables etc, Indexes, or even Blob objects.

This bit of code will show you the objects on a file group.


SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]

FROM sys.indexes i

INNER JOIN sys.filegroups f

ON i.data_space_id = f.data_space_id

INNER JOIN sys.all_objects o

ON i.[object_id] = o.[object_id]

WHERE i.data_space_id = f.data_space_id

AND o.type = 'U' -- User Created Tables



I got this from:

http://blog.sqlauthority.com/2009/06/01/sql-server-list-all-objects-created-on-all-filegroups-in-database/

If there are objects you want to keep you will need to move them to another filegroup. To move a non-clustered index you will need to drop and recreate it on the new file group. To move a table you need to drop and recreate the clustered index.

Once the filegroup has no user objects empty you can run the same commands for removing a file as – shrinkfile and remove file.

Then you will need to run the code below to drop the filegroup:

DATABASE [databaseName] REMOVE FILEGROUP [Filegroupname];

Remember: you can not drop the Primary filegroup.