May 21, 2003 at 10:40 am
Hi there, I run the following query, which shows that no objects exist in a filegroup I want to drop:
SELECT sf.groupid AS GroupID, substring(sf.groupname,1,30) AS FilegroupName, FILENAME,
substring(so.name,1,50) AS ObjectName
FROM sysobjects so
INNER JOIN sysindexes si ON so.id = si.id
INNER JOIN sysfilegroups sf ON si.groupid = sf.groupid
INNER JOIN SYSFILES S ON SF.GROUPID = S.GROUPID
WHERE si.indid = 0 and so.xtype = 'U'
Then when I try to drop the filegroup, I get a message saying that the filegroup cannot be dropped because it contains one or more files. How do I delete the files? Thanks in advance.
May 21, 2003 at 12:27 pm
You should remove the files in the filegroup and then remove the filegroup. Make sure the files you are removing are empty.
May 21, 2003 at 4:24 pm
I believe you can use the ALTER DATABASE command to delete files. BOL has a long section on this command.
Everett Wilson
ewilson10@yahoo.com
May 21, 2003 at 5:05 pm
Thank you for the alter database hint, that is exactly what I needed. Before I delete the file (big step), I want to get the gurus' opinion, if I run the query that I posted in the first message, and it doesn't show me any objects in the filegroup I want to drop, is it safe to say that I can drop the file and filegroup? Thanks a lot!
May 21, 2003 at 5:29 pm
quote:
if I run the query that I posted in the first message, and it doesn't show me any objects in the filegroup I want to drop, is it safe to say that I can drop the file and filegroup?
You have to run dbcc shrinkfile with emptyfile option to ensure the file is empty.
1. Backup your database
2. Run dbcc shrinkfile(yourfilelogicalname, emptyfile) first to ensure file is empty.
3. ALTER DATABASE yourdbname REMOVE FILE yourfilelogicalname
4. ALTER DATABASE yourdbname REMOVE FILEGROUP yourfilegroupname
5. Perform full database backup.
May 21, 2003 at 6:57 pm
THANK YOU!!!
May 22, 2003 at 3:40 pm
Hi there, I ran the dbcc shrinkfile which ran successfuly. The alter database command failed saying that the file cannot be dropped as it is not empty. What do I do now? Thank you.
May 22, 2003 at 8:22 pm
It seems there are text/image columns in some of tables. See following KB help.
http://support.microsoft.com/default.aspx?scid=kb;en-us;279511
http://support.microsoft.com/default.aspx?scid=kb;en-us;324432
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply