First of all Why we need FILEGROUP when you have option to place files in different drives?
FILEGROUP are just logical naming which can help DBA to perform maintenance activities like BACKUP and RESTORE easily. If you set it to READONLY which will reduce the disk and time required to take BACKUP.
If you want to use concept of FILEGROUP backup or Partial Backup or Partial Restore then FILEGROUPS are needed. Also while creating table you have only option to specify to which FILEGROUP it has to be placed. You can take FILEGROUP to Offline.
Remember that tables that are often joined to each other should be in the same filegroup and also keep TABLE and its INDEX in different FILEGROUP which are again located in different physical disk will give good performance improvement (Do some analysis before performing this activity)
Procedure to EMPTYFILE:
Run DBCC SHRINKFILE(3,EMPTYFILE) where by 3 is the NDF file created in PRIMARY or any other FILEGROUP from which contents need to moved to any other file within same filegroup.
Once above command is completed, you will get sometimes error messages like "Server Error Occurred, Results any should be discarded". Now run below command to REMOVE this file since SQL Server no longer allows you to write data into the emptied file
ALTER DATABASE FGTest REMOVE FILE FGTest1
Now you should get "File FGTest1 has been removed". Done!
Possible Error Messages:
Cannot move all contents of file "Filex" to other places to complete the emptyfile operation.
You will additional hints in above message when there is no space available to perform MOVE Operation.
Possible Reasons, Solutions and Workaround:
1. It turns out there is a bug in the SHRINKFILE Command - it has been fixed in SQL 2005 SP3. More info about this bug is at http://support.microsoft.com/default.aspx/kb/959376/ and List of Bugs Fixed in SQL 2005 SP3
2. You cannot drop the first MDF file because it points to all other database files.
3. You dont have enough space within the same filegroup to hold the data.
4. Once you have used the EMPTYFILE keyword on a shrink, you must do a OS close and open on the file before you can drop it. Stopping and restarting SQL Server will do the job.
5. Other option which is Something less invasive is to put the affected database into read-only mode and then back to read-write.
6. You can use Autoclose option and wait for a weekend when there is no users there will be a autoclose happened and you connect to the database to get autoopen and now you can remove the file.
7. When you have ntext/image it can be a problem. There was an old bug in that you need to bcp data out then in to successfully shrink the database. Try select/into a new table; drop the old one; rename the new one back to the old one's name. Then shrink.
