List objects in SQL file

azure-sql-databasefilegroups

We are trying to migrate a database from SQL 2014 to Azure SQL.

In SSMS, you can right click on the database and then on a task to "Deploy Database to Windows Azure SQL Database..".

This task fails because the database uses a SECONDARY filegroup. I've gone through the process to move all indexes, heaps, etc. out of the SECONDARY file group into the PRIMARY filegroup.

However, I still can't delete the file in the SECONDARY filegroup because there is 43MB of data in it. Every script I've found to list all the objects in a filegroup say there is nothing remaining in the file.

How can I determine what's still in this filegroup so I can delete the file and remove the SECONDARY filegroup?

Best Answer

I found this script which lists LOB objects in each filegroup. Based on the results I was able to find the table still using the SECONDARY filegroup.

SELECT
    au.*,
    ds.name AS [data_space_name],
    ds.type AS [data_space_type],
    p.rows,
    o.name AS [object_name]
FROM sys.allocation_units au
    INNER JOIN sys.data_spaces ds
        ON au.data_space_id = ds.data_space_id
    INNER JOIN sys.partitions p
        ON au.container_id = p.partition_id
    INNER JOIN sys.objects o
        ON p.object_id = o.object_id
WHERE au.type_desc = 'LOB_DATA'
Related Question