SQL Server: orphaned files script id files not in partition schemas/indexes. Is returning a file - SQL Server throws saying "not empty"

SQL Server: orphaned files script id files not in partition schemas/indexes. Is returning a file which when delete is attempted, SQL Server throws saying "not empty"

a) what more could I do to this script to return only "orphaned" files - "orphaned" filegroup / file not related to a partition schema or index?

b) if this is enough, how could it be that one of the files returned, when the alter db remove f开发者_开发百科ile is issued - db throws saying "not empty"?

b1) file / filegroup was once part of a partition, but has been merged out. Verified this by sql & by looking @ partition schemas

b2) file shows size of 128, which I have observed as the smallest size possible for a file

WITH cte_file_relations
AS(
SELECT
related_to_nothing = CASE WHEN dataspaces_n_indexes.data_space_id IS NULL
AND dds.partition_scheme_id IS NULL
AND ds_alloc_units.data_space_id IS NULL
AND dataspaces_n_full_text_indexes.data_space_id IS NULL
THEN 1
ELSE 0 END
-- 
, [file_group_name] = fg.name
, filegroup_type_desc = fg.type_desc
, [filegroup_is_empty] = CASE WHEN df.data_space_id IS NULL THEN 1 ELSE 0 END
-- 
, [file_logical_name] = df.name
, [file_phys_name] = df.physical_name
, [file_empty?] = CASE df.size WHEN 128 THEN ''looks empty'' ELSE ''NOT empty'' END
--
, part_of_Partition_Scheme = CASE WHEN dds.partition_scheme_id IS NULL THEN 0 ELSE 1 END
, part_of_index = CASE WHEN dataspaces_n_indexes.data_space_id IS NULL THEN 0 ELSE 1 END
, part_of_full_text_index = CASE WHEN dataspaces_n_full_text_indexes.data_space_id IS NULL THEN 0 ELSE 1 END
, part_of_alloc_units = CASE WHEN ds_alloc_units.data_space_id IS NULL THEN 0 ELSE 1 END
FROM
sys.filegroups fg WITH ( NOLOCK )

LEFT JOIN sys.database_files df WITH ( NOLOCK )
ON fg.data_space_id = df.data_space_id

LEFT JOIN sys.destination_data_spaces dds
ON fg.data_space_id = dds.data_space_id

LEFT JOIN ( SELECT i.data_space_id FROM sys.indexes i GROUP BY i.data_space_id ) dataspaces_n_indexes
ON fg.data_space_id = dataspaces_n_indexes.data_space_id

LEFT JOIN ( SELECT i.data_space_id FROM sys.fulltext_indexes i GROUP BY i.data_space_id ) dataspaces_n_full_text_indexes
ON fg.data_space_id = dataspaces_n_full_text_indexes.data_space_id

LEFT JOIN ( SELECT data_space_id FROM sys.allocation_units GROUP BY data_space_id ) ds_alloc_units
ON fg.data_space_id = ds_alloc_units.data_space_id
)
select *
from cte_file_relations
WHERE related_to_nothing = 1

On a friends hunch, I went to go shrink the file and see of 1 mb total - .88 mb free. Then I looked @ other files I expected to be empty. The majority of them @ 1 mb total space, show .94 mb free space.

Bug in Microsoft Sql Server.

See last post on: http://www.sqlservercentral.com/Forums/FindPost681162.aspx

Tried this in my situation and was able to delete the file.