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.