Post reply

unable to delete empty filegroups (or are they really empty?)

  • March 10, 2004 at 11:44 am

    #107446

    We are unable to delete our empty filegroups!!  This mysterious issue was the result of our filegroup consolidation.  One of our SQL box has over 80 filegroups.  For ease of management, we decided to consolidate these filegroups into 5 filegroups.  We had a few choices of moving data between filegroups, but we choose to run a T-SQL script to re-assign cluster indexes on every tables as our target is to use that method to move the table to the target filegroup that we want.  The process was done, we have moved all our data to those target files, but the end result is that we are ending up with lots of filegroups with 0 files and unable to delete! 

    PROBLEM: Either through enterprise manager or T-SQL.  We are unable to delete these filegroups!!  It complains that we still have files in it!!When I run sp_helpfilegroup it returns filegroups as 0 files on those we wanted to delete.  But when I run sp_helpfile, I see only those files that we wanted to keep.  I also took the groupid from sp_helpfilegroup and try to match in sysindexes table.  Some of them return matching results.

    So what is the problem here? why are we ending up with this mess?

    When we call Alter Database remove filegroup, Where does T-SQL make reference to before it removes filegroup?  Why does it see it not empty? while sp_helpfilegoups say it's empty?

    Does any SQL GURU out there can hit the home run on this?

    thanks

    Jon

    DBA/OLAP Developer -
    San Francisco, CA

  • March 10, 2004 at 1:07 pm

    #498175

    Take a look at SQL Server Central article "Getting Rid of Excessive Files and Filegroups in SQL Server", dated 2/11/2003.  It might be the same snag the author ran into there, system-generated statistics.  Good luck.

  • March 11, 2004 at 7:03 am

    #498272

    Also check in EM(Enterprise Manager) to see which filegroup SQL decided to place any 'Text' data.  Even if you don't have text fields, SQL creates a pointer to those filegroups and you cannot delete them.  I am not sure how to check this from QA (Query Analyzer).

     



    Michelle

  • March 11, 2004 at 10:04 am

    #498330

    Thanks to Sherri for pointing out that article.   I had followed the instructions and remove all the empty files.  I think SQL server create server statistic and I had to drop them and re-create them to my desire filegroup.

    I still have some problem though with one filegroup.  It saids the filegroup has one file, but I can't find any table that uses it???  Not from the table designer!! and I ran the script and there are no server stat. related entries?

    Is this the text or image column on my tables?  I did pullout all the tables that associated to that filegroup, but all their text filegroups are assigned to primary!

    Anymore ideas?

    thanks

    Jon

    DBA/OLAP Developer -
    San Francisco, CA

  • March 11, 2004 at 6:09 pm

    #498422

    Don't worry guys, I got it resolved.  We shrink the data file and after that, we can removed the filegroup!  Strange right? 

    But it worked.

    thanks, TEAM@!!!

    Jon

    DBA/OLAP Developer -
    San Francisco, CA

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

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