I have a database that has 2 Filegroups. I need to move the data from one file group to the other, then delete the filegroup the data was moved from. I know that the filegroup needs to be empty before I can delete it, but each time I run this command, I get this error:
DBCC SHRINKFILE ('myfilegroupname', EMPTYFILE);
But here is the error I keep getting
Msg 2556, Level 16, State 1, Line 8
There is insufficient space in the filegroup to complete the emptyfile operation.
I can see the files in the file group, but I don't know what to do about this error. Should I add space to the database/filegroup/log file? Please advise as I am stuck on this one.
You shrink the file(s) in the filegroup, not the filegroup itself.
You can use sp_helpfile to see the logical file name, or file id, to use in the shrink. SHRINKFILE will accept a file id, which in this case will probably be easier than typing/copying the file name.
I am using the logical name for the filegroup, unless I am misunderstanding your statement. I have dropped the indexes and re-created them in the Primary Filegroup. However I am still getting the error: "There is insufficient space in the filegroup to complete the emptyfile operation" . Right now I only see !MB of space allocated for this file group. Is that an issue?
You need the logical name(s) for the file names in the filegroup, not the filegroup name itself.
I've got that. There's only one file in this filegroup now with a size of 1MB, so I just don't understand why I am getting the error. i have performed a Shrinkfile on that one file. Like I said before I've moved the indexes also, so I'm not sure what the issue is now. Your thoughts?
The emptyfile operation will only move data within a single filegroup - that is, if you have multiple files in the primary filegroup and issue an emptyfile on one of the files - the data will be moved (if space is available) to the other file(s).
The only way to move data between filegroups is to drop the object in the current filegroup - and then create the object in the new filegroup.
If you cannot shrink the file to empty it...that means there is still data in that file. It probably is a HEAP - which doesn't have any indexes that can be dropped and recreated. Identify that object and add a clustered index - created in the new filegroup and that should move the data out of the old filegroup.
so I run the following query to list the objects in the FileGroup:
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 i.data_space_id = 3 -- Filegroup
This query returns 48 rows of data that looks similar to this.
name type name index_id name
fulltext_index_docidstatus_123016811 IT i1 1 ftfg_EDDS1093475
fulltext_index_docidstatus_123016811 IT i2 2 ftfg_EDDS1093475
Not sure where to find this data. As I said before I deleted and recreated all the other indexes into the Primary Filegroup. Right now I need to empty and remove 'ftfg_EDDS1093475'. Like I said there are 48 rows of objects belonging to this filegroup. How do I get rid of this data?
Those are fulltext catalogs - you need to use alter database to move them.