Post reply

unable to delete filegroup

  • May 8, 2006 at 9:59 am

    #172325

    Any ideas?

    I have 2 filegroups in a database. I moved all the data to primary filegroup, deleted the data files from the filegroups. sql server 2000 does not let me remove the filegroup even though there are no datafiles in those 2 filegroups. It gives me an error message.

    Msg 5042, Level 16, State 8, Line 1

    The filegroup 'FG_2005Q2' cannot be removed because it is not empty.

    sp_helpfilegroup - as you can see the groupname, groupid and filecount.

    PRIMARY          1      1

    Dimensions       2      1

    FG_2005Q2       3      0

    FG_2005Q3       5      0

    FG_2005Q4       6      1

    FG_2006Q1       7      1

    FG_2006Q2       8      1

  • May 8, 2006 at 10:11 am

    #636560

    there is still the brutal force way of doing it:

    sp_configure 'allow updates',1

    go

    reconfigure with override

    go

    use (your database)

    delete from sysfilegroups where groupname ='(your filegroup)

    go

    sp_configure 'allow updates',0

    go

    reconfigure with override

    go

    but before doing that make a full backup of your db.

    regards,

    Holger

  • May 8, 2006 at 11:09 am

    #636575

    I am worried about DB corruption. It's a production database 450GB in size.

    Thanks for the idea. I can try it on dev and see if it works.

    When I restore the db on dev using "with move", it does not recognize the 2 filegroups. So maybe the are not a part of the database anymore.

  • May 8, 2006 at 11:55 am

    #636582

    It can also be that a restart of the SQL Server service solve the problem.

    But it is always a good idea to try it on a test machine first. I've done it once and it has worked fine.

  • May 26, 2006 at 12:34 pm

    #640425

    you have to remove individual files within the filegroup before you can remove the filegroup itself.

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

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