May 24, 2013 at 6:26 am
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?
May 24, 2013 at 6:43 am
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
May 24, 2013 at 6:45 am
Thank you, Ma'am. I knew it had to be simple.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply