Remove a File and Filegroup from Database

Posted: May 5, 2010 in DBA, Filegroups, Server Maintenance

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s