Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

check object list and size in filegroup

Posted by Simon Cho on 06/08/2011

select FILEGROUP_NAME(i.data_space_id) as file_group

, o.name

, i.index_id

, i.name as index_name

, s.size_MB

from sys.indexes i

join sys.all_objects o

on i.[object_id] = o.[object_id] and o.type=’U’

cross apply

(select convert(numeric(10,2),

SUM(in_row_data_page_count

+ lob_used_page_count

+ row_overflow_used_page_count) * 8/1024.0) size_MB

from sys.dm_db_partition_stats where object_id=o.object_id and index_id=i.index_id) s

order by 1,2,3

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: