I am looking to eliminate an extraneous data file in a filegroup in my DB but don't want to use the dreaded DBCC SHRINKFILE method, I am preferring to rebuild indexes into an alternate FG, perform the shrink with EMPTYFILE followed with removal of the file and then rebuild back into the original filegroup. Is there a method to identify which table/index objects are populating the utilized pages/extents in the target database file?
Sql-server – How to Determine Object Distribution in a Multiple-File Filegroup
data-pagesdbccshrinksql server
Related Question
- Sql-server – Splitting a large SQL Server MDF file
- Why does FREEPROCCACHE let you shrink tempdb
- Sql-server – Best Way to Reduce the Size of 8 TB DB
- Sql-server – How to save the results of DBCC SHRINKFILE into a table
- Sql-server – When shrinking log files what difference does TRUNCATEONLY make
- Sql-server – SQL Server: Identifying object closest to the end of a datafile
- Sql-server – Filegroup: [primary] dbcc shrinkfile() need balanced extent counts per file when done
- Sql-server – Static archive database: SQL Server can’t shrink it and leaves ~400G of unallocated space
Best Answer
You should assume, for all intents and purposes, that parts of every object in a given filegroup will be distributed across all of the files in that filegroup. You might be able to work up some script that will prove one or two smaller tables don't have any data on file 5 or file 8, but for what purpose? I say it's easier to just write a script that will rebuild all of the tables in that filegroup.