How to tell what objects are on what filegroups

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

How to tell what objects are on what filegroups

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-03-06 : 04:32:36

Is there a way to identify what tables are placed on which filegroups? The reason being is that one filegroup is 15gb in size, and another is 7gb, but they should contain almost the same amount of data?

Thanks

Hearty head pats

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2009-03-06 : 06:29:48

SELECT
fg.data_space_id, fg.name,
ObjectName = OBJECT_NAME(p.object_id), p.index_id
,df.name, df.physical_name, [Size] = df.size*8/1024
FROM sys.filegroups fg
LEFT JOIN sys.database_files df
ON fg.data_space_id = df.data_space_id
LEFT JOIN sys.partitions p
ON fg.data_space_id = p.partition_number
WHERE (p.object_id>4096 or p.object_id IS NULL)

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-03-06 : 06:38:13

Thankyou! Just what I needed!

Hearty head pats

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2009-03-06 : 06:42:11

cheers

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-03-06 : 07:18:27

Hi there

The query was yeilding some strange results (everything appeared on the PRIMARY filegroup and the others were empty - which I knew was not correct).

Anyway, I've written another version below. Thought I would post it in case anyone else requires a similar function:

SELECT tbls.[name] AS TableName
, inds.[name] AS [Index Name]
, CASE is_primary_key WHEN 1 THEN 'Table Rows' ELSE 'Index Rows' END AS [Type of Data]
, data.[name] AS [Filegroup Name]
, files.type_desc
, files.[size] AS [Current file size in KB]
, files.physical_name
FROM sys.indexes inds
INNER JOIN sys.tables tbls ON tbls.object_id = inds.object_id
INNER JOIN sys.data_spaces data ON data.data_space_id = inds.data_space_id
INNER JOIN sys.database_files files ON files.data_space_id = data.data_space_id
WHERE tbls.type = 'u'
ORDER BY [Filegroup name]

Hearty head pats

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2009-03-06 : 07:42:25

goog work

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-03-06 : 09:52:03

Lol, thanks

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources