Post reply

Finding a table's file

  • May 24, 2013 at 6:26 am

    #146061

    I have a database with 3 files on 3 different file groups. They are divided amongst 2 drives. One of those drives is nearly full.

    My task is to locate what tables are on the file / filegroup that is on the nearly full drive so I can appropriately solve the problem. More HD space is not forthcoming as this server is near end of life.

    Try as I might, my google-fu does not point me in the correct direction for identifying the files upon which the tables reside. Anyone have any suggestions?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • May 24, 2013 at 6:43 am

    #1618236

    SELECT OBJECT_NAME(object_id) AS TableName, i.index_id, i.name, ds.name

    FROM sys.indexes i INNER JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id

    The indexes and the filegroups they are on.

    If an index is on a filegroup, it should be assumed that it's pages are spread across all the files in the filegroup.

    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

  • May 24, 2013 at 6:45 am

    #1618240

    Thank you, Ma'am. I knew it had to be simple.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

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

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