Can't delete filegroup, even though it is empty

Hi All,
I have a SQL 2000 database that I had created a seperate file group for to
hold indexes. I have since deleted the indexes and want to delete the file
groups. When I execute the ALTER DATABASE <dbname> REMOVE FILEGROUP
<filegroupname> I receive an error indicating that 'The filegroup <filegroup>
cannot be removed because it is not empty'
If have verified that there are no tables or indexes in this filegroup and
have also run the following below queries to verify that the given filegroups
are empty:
select name,groupid
from sysindexes
where groupid=filegroup_id(<groupname>)
and indid > 0
Although there are no objects residing in the actual devices, they are still
listed in sysfiles. As such, even if I try to detach and reattach the DB,
the files are still linked and loaded with the database.
The tables to which the indexes existed are very large, so I also
stopped/restarted the sql services (thinking that possibly something may have
been stuck in memory) however that did not resolve the problem.
Has aonyone encountered this before? Is anyone aware of a fix for this?
Thanks,
You can try using DBCC SHRINKFILE with the EmptyFile
option first. Then use ALTER DATABASE to remove the filegroup.
"DBADave" <DBADave@.discussions.microsoft.com> wrote in message
news:703563A7-BF30-445B-B563-2E0C323A9957@.microsoft.com...
> Hi All,
> I have a SQL 2000 database that I had created a seperate file group for to
> hold indexes. I have since deleted the indexes and want to delete the
file
> groups. When I execute the ALTER DATABASE <dbname> REMOVE FILEGROUP
> <filegroupname> I receive an error indicating that 'The filegroup
<filegroup>
> cannot be removed because it is not empty'
> If have verified that there are no tables or indexes in this filegroup and
> have also run the following below queries to verify that the given
filegroups
> are empty:
> select name,groupid
> from sysindexes
> where groupid=filegroup_id(<groupname>)
> and indid > 0
> Although there are no objects residing in the actual devices, they are
still
> listed in sysfiles. As such, even if I try to detach and reattach the DB,
> the files are still linked and loaded with the database.
> The tables to which the indexes existed are very large, so I also
> stopped/restarted the sql services (thinking that possibly something may
have
> been stuck in memory) however that did not resolve the problem.
> Has aonyone encountered this before? Is anyone aware of a fix for this?
> Thanks,
|||I have tried this. The DBCC shows no output other then the standard DBCC
execution statement. When I attempt to issue an ALTER DATABASE to drop the
filegroups I get the error indicating that the filesgroups are not empty.
There are no objects in these filegroups, sysfilesgroups also does not show
the filegroups although sysfiles and sysfiles1 still show the filegroups that
I am trying to remove.
Any help would be greatly appreciated.
Thanks,
"Armando Prato" wrote:

> You can try using DBCC SHRINKFILE with the EmptyFile
> option first. Then use ALTER DATABASE to remove the filegroup.
> "DBADave" <DBADave@.discussions.microsoft.com> wrote in message
> news:703563A7-BF30-445B-B563-2E0C323A9957@.microsoft.com...
> file
> <filegroup>
> filegroups
> still
> have
>
>
|||This is kind of weird, but if i manually add the filegroup records back to
sysfilesgroups I can then see that there is an empty file in each (also
verified in EM by then looking at the filegroups properties to see the number
of files within). I was then able to ALTER DATABASE with a REMOVE FILE
refernecing what the original file name was, then issue an ALTER DATABASE
with a REMOVE FILEGROUP.
I am all set now.
Thanks All,
Dave
"DBADave" wrote:
[vbcol=seagreen]
> I have tried this. The DBCC shows no output other then the standard DBCC
> execution statement. When I attempt to issue an ALTER DATABASE to drop the
> filegroups I get the error indicating that the filesgroups are not empty.
> There are no objects in these filegroups, sysfilesgroups also does not show
> the filegroups although sysfiles and sysfiles1 still show the filegroups that
> I am trying to remove.
> Any help would be greatly appreciated.
> Thanks,
>
> "Armando Prato" wrote:
|||Have you tried a DBCC SHRINKFILE with EMPTYFILE on the member files and then
dropping the files first?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"DBADave" <DBADave@.discussions.microsoft.com> wrote in message
news:95372080-4621-4B1B-9F3E-907D7503D8B9@.microsoft.com...
I have tried this. The DBCC shows no output other then the standard DBCC
execution statement. When I attempt to issue an ALTER DATABASE to drop the
filegroups I get the error indicating that the filesgroups are not empty.
There are no objects in these filegroups, sysfilesgroups also does not show
the filegroups although sysfiles and sysfiles1 still show the filegroups
that
I am trying to remove.
Any help would be greatly appreciated.
Thanks,
"Armando Prato" wrote:

> You can try using DBCC SHRINKFILE with the EmptyFile
> option first. Then use ALTER DATABASE to remove the filegroup.
> "DBADave" <DBADave@.discussions.microsoft.com> wrote in message
> news:703563A7-BF30-445B-B563-2E0C323A9957@.microsoft.com...
> file
> <filegroup>
> filegroups
> still
> have
>
>