I am trying to empty a file in order to remove it from database. First of all, I moved the tables and indexes with creating clustered and nonclustered indexes with DROP_EXISTING = ON in another filegroup. To ensure that no other table and index exists in the file I ran this query:

SELECT FILEGROUP_NAME(AU.data_space_id) AS FileGroupName,
        OBJECT_NAME(Parti.object_id) AS TableName,
        ind.name AS ClusteredIndexName,
        AU.total_pages/128 AS TotalTableSizeInMB,
        AU.used_pages/128 AS UsedSizeInMB,
        AU.data_pages/128 AS DataSizeInMB
    FROM sys.allocation_units AS AU
    INNER JOIN sys.partitions AS Parti ON AU.container_id = CASE WHEN AU.type in(1,3) THEN Parti.hobt_id ELSE Parti.partition_id END
    LEFT JOIN sys.indexes AS ind ON ind.object_id = Parti.object_id AND ind.index_id = Parti.index_id
    LEFT JOIN sys.objects o on ind.object_id = o.object_id
    WHERE FILEGROUP_NAME(AU.data_space_id) = 'MyFileGroupName' 
    ORDER BY AU.data_space_id DESC

It returnes No result. But when I try to remove the file, it says that file is not empty yet.

I try to investigate the file content using this command:


The output is a few record which can be categorized into 2 group. The first one contains page headers and the other records are something like:

ParentObject: BUFFER:  
Object: BUF @0x000001D878E2FCC0

I can't empty file by SHRINK EMPTYFILE because there is no more file in filegroup. Also I am going to remove the filegroup too.

  • See if Removing a FILESTREAM Container helps.
    – Ronaldo
    Apr 21, 2022 at 2:36
  • @Ronaldo Thank you. But my database does not contain FILESTRAM. Apr 21, 2022 at 13:33
  • If your table(s) had a LOB column, simple creating/rebuilding a clustered index does not move the column in the new filegroup. To move LOB data you should create the table on a partition schemе. Apr 22, 2022 at 6:17


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.