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 a table's file Expand / Collapse
Author
Message
Posted Friday, May 24, 2013 6:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 9:11 AM
Points: 7,128, Visits: 6,291
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1456441
Posted Friday, May 24, 2013 6:43 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:50 AM
Points: 42,451, Visits: 35,506
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 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 #1456456
Posted Friday, May 24, 2013 6:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 9:11 AM
Points: 7,128, Visits: 6,291
Thank you, Ma'am. I knew it had to be simple.

Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1456460
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse