Empty / Ophaned files

  • 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.

  • Bug in SQL Server. Tried workaround @ end of post. It worked, was able to delete the file.

    http://www.sqlservercentral.com/Forums/FindPost681162.aspx

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply