Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I wanted to remove some unused filegroups/files in a SQL Server Database but am stuck because SQL Server thinks the filegroups/files are still in use.

Some background:

  • We had some partioned tables that we converted back to non-partitioned ones
  • All partition-functions and -schemes were deleted
  • I queried the dm views for unused filegroups like this:

    SELECT 
    *
    FROM sys.filegroups fg
    LEFT OUTER JOIN 
    sysfilegroups sfg
    ON fg.name = sfg.groupname
    LEFT OUTER JOIN 
    sysfiles f
    ON sfg.groupid = f.groupid
    LEFT OUTER JOIN 
    sys.indexes i
    ON fg.data_space_id = i.data_space_id
    WHERE i.object_id IS NULL
    

    This got me a list of filegroups/files i tried to remove from the database. But some of them could not be removed. Example error message:

    The filegroup 'FG_XXXX' cannot be removed because it is not empty.
    
  • I was not able to get the connection to tables/indexes for these filegroups via sys.data_spaces and sys.indexes:

    SELECT * FROM  sys.data_spaces ds
    INNER JOIN sys.indexes i
    ON ds.data_space_id = i.data_space_id
    WHERE ds.name = 'FG_XXXX'
    
  • I am aware that a filegroup can not be removed if it is referenced in some partition scheme.
    But this cannot be the reason here, as i deleted all partition schemes/functions in the db.

Any hints what i could do to get rid of the filegroups?

share|improve this question

3 Answers 3

You may have some LOB data (text/image/varchar(max)/nvarchar(max)) sitting in the filegroup still. I got caught up by this briefly not too long ago. Rebuilding a table/index on a different filegroup/partition does not move any of the LOB data.

SELECT
    au.*,
    ds.name AS [data_space_name],
    ds.type AS [data_space_type],
    p.rows,
    o.name AS [object_name]
FROM sys.allocation_units au
    INNER JOIN sys.data_spaces ds
        ON au.data_space_id = ds.data_space_id
    INNER JOIN sys.partitions p
        ON au.container_id = p.partition_id
    INNER JOIN sys.objects o
        ON p.object_id = o.object_id
WHERE au.type_desc = 'LOB_DATA'

If that's indeed what's happening, the simplest way to move the LOB data is by recreating the table. You can move the data either with bcp out/in, or by inserting directly into a new table and renaming afterward (or by any other preferred method of moving the data you have). Make sure you specify the correct filegroups for row and text data when creating the new table.

share|improve this answer
    
Thanks for the answer, but there is no LOB data in the respective filegroups. Additionally, the table/partition scheme that pointed to the filegroup(s) are deleted. –  sarnu Jul 31 '13 at 8:31

Try using sys.allocation_units instead of sys.indexes. BOL says it's for internal use only so I wouldn't write any long term code on it but for this purpose it should be ok. It points to the first IAM page of each allocation unit. I believe it will work better for what you are trying to do.

SELECT *
FROM sys.filegroups fg
LEFT OUTER JOIN sysfilegroups sfg
    ON fg.name = sfg.groupname
LEFT OUTER JOIN sysfiles f
    ON sfg.groupid = f.groupid
LEFT OUTER JOIN sys.allocation_units i
    ON fg.data_space_id = i.data_space_id
WHERE i.data_space_id IS NULL

Also as @RobertGannon said you can right click on the name of your database, then go to tasks, shrink, files

enter image description here

You can then select the "Empty file by migrating the data to other files in the same filegroup" to get all of the data off of a given file. This won't help you get rid of a filegroup itself, but it will let you condense it down to a single file.

enter image description here

share|improve this answer
    
Did you mean to write sys.system_internals_allocation_units instead of sys.allocation_units? The answer doesn't say how to proceed with the IAM information... –  Paul White Jul 29 '13 at 23:56
    
@PaulWhite, I may have misspoken about the IAM pages. I read allocation unit and got IAM. As far as what to do with it, nothing really. It just let's you know that there is data in the filegroup. The OP was looking for unused filegroups. Using the sys.allocation_units seems to give a better indication of the filegroups that used/unused. You could of course note those with only allocation units marked as "dropped" and check them again later, but that seems to be the extent of the data provided by the system view. –  Kenneth Fisher Jul 30 '13 at 4:09

Shrink the files and make sure they are not the default filegroup. Shrinking should allow you to verify that the file is empty using EMPTYFILE. SQL Server will not allow you to remove the default file group.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.