none
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

    Monday, April 30, 2012 1:26 AM

Answers

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




    • Edited by Rahul Bobal Monday, April 30, 2012 8:31 AM
    • Marked as answer by Rahul Bobal Monday, April 30, 2012 8:31 AM
    Monday, April 30, 2012 8:27 AM

All replies

  • 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

    Monday, April 30, 2012 1:37 AM
    Moderator
  • 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.

    Monday, April 30, 2012 2:05 AM
  • 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.


    Monday, April 30, 2012 2:58 AM
  • 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.




    • Edited by Rahul Bobal Monday, April 30, 2012 8:31 AM
    • Marked as answer by Rahul Bobal Monday, April 30, 2012 8:31 AM
    Monday, April 30, 2012 8:27 AM