February 8, 2011 at 8:03 am
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 file 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.
February 8, 2011 at 8:56 am
Bug in SQL Server. Tried workaround @ end of post. It worked, was able to delete the file.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply