Files and Filegroups: how to remove a file

Question

Files and Filegroups: how to remove a file

SQL Guy 1

SSCoach

Points: 15979
More actions
July 21, 2010 at 12:53 pm

#223517

We need to remove a file that was created some time ago in our database. This file belongs to PRIMARY filegroup, that's besides the default file that was created when database was first created.
We need to remove this "non-default" file. We get a message "The file 'file_name' cannot be removed because it is not empty."
Tables and indexes are stored in filegroups, but files are beyond our control. I'm curious whether there is a command in SQL Server that will transfer all data from file to file within same filegroup ? Or maybe I can achieve it by some other methods ?
Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 1

Check this from the below MS link
http://technet.microsoft.com/en-us/library/ms189493.aspx
Emptying a file
The following example demonstrates the procedure for emptying a file so that it can be removed from the database. For the purposes of this example, a data file is first created and it is assumed that the file contains data.
USE AdventureWorks2008R2;
GO
-- Create a data file and assume it contains data.
ALTER DATABASE AdventureWorks2008R2
ADD FILE (
NAME = Test1data,
FILENAME = 'C:\t1data.ndf',
SIZE = 5MB
);
GO
-- Empty the data file.
DBCC SHRINKFILE (Test1data, EMPTYFILE);
GO
-- Remove the data file from the database.
ALTER DATABASE AdventureWorks2008R2
REMOVE FILE Test1data;
GO

Thank You,

Best Regards,

SQLBuddy

SQL Guy 1 SSCoach Points: 15979 More actions · Answer 2

Thanks a lot !

So the main point that I did not know about is:

DBCC SHRINKFILE (Test1data, EMPTYFILE);

Everything else worked well after executing this. Thanks