Remove filegroup | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Remove filegroup

Hello, I try to remove a filegroup from my database, before that I removed all objects from the filegroup and then I executed the script in blue and I got the next error:
Script:
DBCC SHRINKFILE (FG1_7_Data,EMPTYFILE)
ALTER DATABASE XDOCS REMOVE FILE FG1_7_Data
GO Error:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Server: Msg 5042, Level 16, State 1, Line 2
The file ‘FG1_7_Data’ cannot be removed because it is not empty.
Anyone has an idea ?
Thanks in advance,
Oryan
Don’t know which version of SQL is used, actually shrink code line in the SQL Server 7.0 product does not account for empty text or image extents created when multiple deletions take place on the same extent simultaneously. Books Online (BOL) documents the REMOVE FILE option of ALTER DATABASE, but BOL doesn’t tell you that you first need to run SHRINKFILE(‘file_name’, EMPTYFILE). This command shrinks the file to 0MB of space used and ensures that you’ve removed all the objects from the file. After you use this command, you can get rid of that old file.
And after the DBCC statement input GO in order to complete the empty file process. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
The version of SQL : SQL Server 2000 Enterprise Edition (sp3)
I added ‘GO’ after the DBCC statement and it still doesn’t work (same error). Oryan
Check what objects are still stored under this filegroup.
SP_HELPFILEGROUP for filecount. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
I cheked and I didn’t find objects on the filegroup. select name,FG
from ( select name ,
( SELECT s.groupname FROM dbo.sysfilegroups s, dbo.sysindexes i WHERE i.groupid = s.groupid AND i.id = object_id(sysobjects.name) AND i.indid in (0, 1)) as FG
from sysobjects
where xtype=’U’) as TMP
where fg = ‘FG1_7’
The filegroup has 1 file, so I tried to execute:
ALTER DATABASE XDOCS REMOVE FILEGROUP FG1_7
and still same error:
Server: Msg 5042, Level 16, State 7, Line 1
The filegroup ‘FG1_7’ cannot be removed because it is not empty. Oryan
Is this a primary filegroup? Try from EM:
To remove the second file. Backup the database, in Enterprise Manager right click the database, select all tasks, shrink database, then files and choose the second file in the drop down and select Empty the file (Data will migrate to other files in the file group) option and click OK. When this has finished, using Enterprise Manager right click the database, select properties and look at the data files. Select the second file you just emptied and choose Delete. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Also try to find any statistics or hind related to that filegroup, if any exist you have to delete it, before delete filegroup.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
]]>