1

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?

1

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'
-1

Can you post the result of the following sql statement?

SELECT o.[name], o.[type], o.[type_desc], i.[name], i.[index_id], f.[name] 
FROM sys.indexes i
INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id] 
WHERE i.data_space_id = f.data_space_id
AND i.data_space_id > 1
GO
  • no rows return from the query – Pattison Apr 29 '16 at 17:46
  • in that case, the secondary file group has no object in it, i would suggest you to make a backup, then remove the file group by right click on the db, then click Properties, then click Filegroups, then remove the SECONDARY filegroup, and retry to the ""Deploy Database to Windows Azure SQL Database.." operation. – sammo May 3 '16 at 1:48
  • If that does not help, can you try the opposite way by scripting the entire object to see what locates in the secondary file group? Right click on the db, click 'Tasks', then click 'Generate Scripts...', select 'Script entire database', then save to file or clipboard or new query window where you see fit. Then search through the output for the secondary file group. Hope this helps! – sammo May 3 '16 at 6:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.