Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Finding contents of logical file Expand / Collapse
Posted Tuesday, February 18, 2014 7:51 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 10:37 AM
Points: 71, Visits: 188
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?

Post #1542583
Posted Tuesday, February 18, 2014 7:55 AM This worked for the OP Answer marked as solution



Group: General Forum Members
Last Login: Today @ 2:55 PM
Points: 41,570, Visits: 34,495
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 2008, MVP
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

Post #1542588
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse