I am working on cleaning data saved in SQL filegroups monthly , where I delete data for one month and then shrink the data filegroup to save space, noting that filegroup is based on date, each month has its' own filegroup.
But this time after deletion done , I noticed there still data in filegroup where the rows are not zero as a result of below query , but I can't determine what are the rows saved there. The shrink didn't work because the free space is not 100%.
So my question is how I can know what is still saved in specific filegroup ?
```
use dhpo
SELECT
FileGroup = FILEGROUP_NAME(a.data_space_id) ,
TableName = OBJECT_NAME(p.object_id) ,
IndexName = i.name ,
p.rows,
8 * SUM(a.used_pages) AS 'Size(KB)' ,
8 * SUM(a.used_pages) / 1024 AS 'Size(MB)' ,
8 * SUM(a.used_pages) / 1024 / 1024 AS 'Size(GB)'
FROM
sys.allocation_units a
INNER JOIN sys.partitions p
ON a.container_id = CASE WHEN a.type IN ( 1 , 3 ) THEN p.hobt_id
ELSE p.partition_id
END
AND p.object_id > 1024
LEFT JOIN sys.indexes i
ON i.object_id = p.object_id
AND i.index_id = p.index_id
where FILEGROUP_NAME(a.data_space_id)='filegroupName'
GROUP BY
a.data_space_id ,
p.object_id ,
i.object_id ,
p.rows,
i.index_id ,
i.name
ORDER BY
OBJECT_NAME(p.object_id),
FILEGROUP_NAME(a.data_space_id) ,
SUM(a.used_pages) DESC; ```
