I have a database using two filegroups. Let's call them PRIMARY and FG1. All data was originally in PRIMARY and then moved to be in FG1. This was achieved by moving the clustered indexes to FG1. The other indexes were removed and recreated without specifying a file group, but FG1 was defined as default so they are now effectively in FG1.
However, the file for PRIMARY still gets filled over time.
How can I find the type of stuff that remains in PRIMARY, including its size?
My goal is to get really everything over to FG1 so that PRIMARY doesn't get filled anymore.
Here's a script which lists all objects and all indexes in all filegroups:
-- List all Objects and Indexes -- per Filegroup / Partition and Allocation Type -- including the allocated data size SELECT DS.name AS DataSpaceName ,AU.type_desc AS AllocationDesc ,AU.total_pages / 128 AS TotalSizeMB ,AU.used_pages / 128 AS UsedSizeMB ,AU.data_pages / 128 AS DataSizeMB ,SCH.name AS SchemaName ,OBJ.type_desc AS ObjectType ,OBJ.name AS ObjectName ,IDX.type_desc AS IndexType ,IDX.name AS IndexName FROM sys.data_spaces AS DS INNER JOIN sys.allocation_units AS AU ON DS.data_space_id = AU.data_space_id INNER JOIN sys.partitions AS PA ON (AU.type IN (1, 3) AND AU.container_id = PA.hobt_id) OR (AU.type = 2 AND AU.container_id = PA.partition_id) INNER JOIN sys.objects AS OBJ ON PA.object_id = OBJ.object_id INNER JOIN sys.schemas AS SCH ON OBJ.schema_id = SCH.schema_id LEFT JOIN sys.indexes AS IDX ON PA.object_id = IDX.object_id AND PA.index_id = IDX.index_id ORDER BY DS.name ,SCH.name ,OBJ.name ,IDX.name
Thanks @Raphaël Althaus for the link in the comments on the question.
Regarding the second point in my question I couldn't move the remaining objects from PRIMARY as it is LOB data. According to the documentation of CREATE TABLE, "the storage of any large column data specified in CREATE TABLE cannot be subsequently altered." Bad luck.