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:
DBCC PAGE ('DB_NAME', 1, 3) WITH TABLERESULTS
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.