Post reply

Empty file group error

  • April 25, 2018 at 9:05 pm

    #334564

    I am trying to remove files from an old filegroup (data files ) which we no longer use .
    when i use the below query i see there are no objects present in this filegroup .
    query 1 used :
    SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name] FROM sys.indexes i
    INNER JOIN sys.filegroups f
    ON i.data_space_id = f.data_space_id
    INNER JOIN sys.all_objects o
    ON i.[object_id] = o.[object_id] WHERE i.data_space_id = f.data_space_id
    AND o.type = 'U' -- User Created Tables
    GO
    but when i double verify with another query 2 using sys.partitions i see 4 entries which might be the reason i am not able to empty this filegroup totally
    select * from sys.partitions p
    inner join sys.allocation_units a on a.container_id = p.hobt_id
    inner join sys.filegroups f on f.data_space_id = a.data_space_id
    where f.name='TEXT01'

    I can find the table details but i still cant see what is kept in this filegroup except for few details . So if i still wish to proceed and empty the file , what steps should i take ?

  • April 26, 2018 at 11:41 am

    #1988392

    muzikfreakster - Wednesday, April 25, 2018 9:05 PM

    I am trying to remove files from an old filegroup (data files ) which we no longer use .
    when i use the below query i see there are no objects present in this filegroup .
    query 1 used :
    SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name] FROM sys.indexes i
    INNER JOIN sys.filegroups f
    ON i.data_space_id = f.data_space_id
    INNER JOIN sys.all_objects o
    ON i.[object_id] = o.[object_id] WHERE i.data_space_id = f.data_space_id
    AND o.type = 'U' -- User Created Tables
    GO
    but when i double verify with another query 2 using sys.partitions i see 4 entries which might be the reason i am not able to empty this filegroup totally
    select * from sys.partitions p
    inner join sys.allocation_units a on a.container_id = p.hobt_id
    inner join sys.filegroups f on f.data_space_id = a.data_space_id
    where f.name='TEXT01'

    I can find the table details but i still cant see what is kept in this filegroup except for few details . So if i still wish to proceed and empty the file , what steps should i take ?

    As a guess, you moved tables to another filegroup with alter table/create clustered index. LOB data doesn't move when you do that and that's what you have those listed as being in that filegroup you want to empty. If that's the case, you can find more info in this article as well as an explanation on how to move LOB data:
      What about moving LOB data?

    Sue

Viewing 2 posts - 1 through 1 (of 1 total)

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