Post reply

Finding contents of logical file

  • February 18, 2014 at 7:51 am

    #283803

    I have multiple filegroups on a database and one has two logical files in it. I want to remove one of these files but I get the error: The file 'filename' cannot be removed because it is not empty. I can tie the objects in the database back to sys.filegroups and then sys.database_files, but both files in this filegroup have the same data_space_id. Is there a way to tie a given object back to a specific file_id or file_guid? If so, how?

    TIA

  • February 18, 2014 at 7:55 am

    #1689918

    Objects aren't defined on files, they're defined on filegroups. If you have two files in a filegroup, all objects on that filegroup will be spread across both files.

    If you want to drop one of the files in a filegroup, you need to use DBCC ShrinkFile with the EMPTYFILE option, that will move all data to the other file in the filegroup and allow you to drop the shrunk file.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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