Problem with removing FileGroups

Question

Problem with removing FileGroups

RSingh-135921

Default port

Points: 1438
More actions
December 6, 2006 at 7:03 am

#61958

We have a database where we have 15 filegroups.

When I try to remove a certain filegroup it gives me this error:
Server: Msg 5042, Level 16, State 7, Line 1
The filegroup 'FG_W20060730' cannot be removed because it is not empty.

I dropped all the tables that were there in this filegroup but even after that if I try to remove the filegroup it gives me the above mentioned error.

There is one file in this filegroup. If I try to remove this file, I get this error:
Server: Msg 5042, Level 16, State 1, Line 1
The file 'FL_W20060730' cannot be removed because it is not empty.

What data could still be residing in the file/filegroup even though I have dropped all the tables in it.

Here is what I have already tried:
DBCC SHRINKFILE (FL_W20060730, EMPTYFILE)
DBCC SHRINKFILE (FL_W20060730, TRUNCATEONLY)

But my attempts to remove the file from the database always return "file not empty error"

Can someone pls help here?

Regards,
RSingh

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

the sql below might help; it identifies objects and the filegroup they belong to;maybe something is not moved off of the filegroup yet?

select sysobjects.name as TableName,

s.groupname as Data_located_on_filegroup

from sysobjects, sysfilegroups s, sysindexes i

where

sysobjects.id = i.id

and i.indid < 2

and i.groupid = s.groupid

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

RSingh-135921 Default port Points: 1438 More actions · Answer 2

Thanks Lowell for the reply.

When I ran your query it returned 0 rows.

But I modified it a little and it helped me:

select sysobjects.name as TableName,

s.groupname as Data_located_on_filegroup

from sysobjects, sysfilegroups s, sysindexes i

where

sysobjects.id = i.id

and i.groupid = s.groupid

After running this query I found that there were some non-clustered indexes that were created on this filegroup but the parent tables were there on some other filegroup.

Since these tables were also not required, I dropped them and after that the file and filegroup have been successfully removed from the Database.

Thanks for your help

RSingh

MohammedU SSCoach Points: 19644 More actions · Answer 3

Don't forget to take full backup without any delay after major modifications to the db.

MohammedU
Microsoft SQL Server MVP

Lowell SSC Guru Points: 323518 More actions · Answer 4

glad it helped, RSingh;

i was thinking along those same lines awaiting your reply...maybe some indexes are in the filegroup, but not the objects they index;

way to go!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!