How to Find "Table Name" in particular "Data File"

Question

Hello Fellow DBAs

Sql Version:    MS SQL Server 2005 + Service Pack 3

Situation: ------

I have a Database "DEV" with 4 Data Files.

1. Dev_1.Mdf
2. Dev_2.ndf
3. Dev_3.ndf
4. Dev_4.ndf

All above mentioned files are in Default Filegroup - "PRIMARY"

I want to Empty "Dev_4.ndf" and delete it. so ran  DBCC SHRINKFILE with EMPTYFILE option - to empty it  
BUT, even after I ran this command it didn't empty the file.  

I would like to know 

How to Find "Table/Objects"  which are residing in this particular Data File.

any Help would be appreciated. 

Thanks
R

Answer 1

Tables are mapped to filegroup not physical file. Below should provide little help.

SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]
FROM sys.indexes i
INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id]
WHERE i.data_space_id = f.data_space_id
AND o.type = 'U' -- Remove this if you want to see other objects

Whats the error you are getting when you try  to remove the file? 

---

Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog | Team Blog | @Twitter

Answer 2

Hello BalMukund,

is there a way we can find out which table / object is in which file id?

I get error when I actually try to remove it...using ALTER command..

it says File cannot be removed because its not empty.

then when I check the space utilization of file DEV_4.ndf it shows its occupying  4 MB of data...

now I want to know that 4MB belongs to which table.

Answer 3

Below is the error I am getting.

Msg 2555, Level 16, State 2, Line 1
Cannot move all contents of file "DEV_4" to other places to complete the emptyfile operation.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Answer 4

Finally FOUND THE SOLUTION  

To find which object is part of which Data file....information can be obtained by executing DBCC EXTENTINFO ('<Database Name>')
by executing the above command we get the following output:

------------------------------------------------------------------------------------
File_id, Page_id,  pg_alloc , ext_size , object_id , index_id , partition_number, partition_id , iam_chain_type , pfs_bytes 

------------------------------------------------------------------------------------

I inserted the output into a Temp table and then ran a query to find the object based on the datafile id.
---->  select  object_name(object_id) from #Object_Search where File_id=  <give file id number>   <----

Create Table #Object_Search(

File_id Bigint,
Page_id Bigint,
pg_alloc Bigint,
ext_size Bigint,
object_id Bigint,
index_id Bigint,
partition_number Bigint,
partition_id Bigint,
iam_chain_type Varchar(50),
pfs_bytes varchar(50)
)

Insert into #Object_Search
EXEC('DBCC EXTENTINFO(''<Database Name>')')

--Drop table #Object_Search

--File id for my data file, DEV_4, is 5 therefore I just queried for that--

select  object_name(object_id),* from #Object_Search where File_id=5

After I followed the above steps I was able to find the object and move it to other Data file.
moving the object can be done either by dropping/recreating index or by creating the index in different filegroup which is your choice.