October 13, 2012 at 8:30 am
I have this large filegroup with 45 GB of data, but when I run the following script to get which tables/indexes are in that filegroup I get no results.
Anyone can see what I am missing?
SELECT
schema_name(t.schema_id) as schemaName
,object_name(p.object_id) as table_name
,i.[name] AS index_name
,f.name as fileGroupName
,p.used_page_count
,p.row_count
,p.used_page_count * 8192 / (1024 * 1024) AS Size_MB
FROM
sys.dm_db_partition_stats p
INNER JOIN
sys.tables t
ON
p.object_id = t.object_id
LEFT OUTER JOIN
sys.sysindexes as i
ON
i.id = p.object_id
and i.indid = p.index_id
INNER JOIN
sys.filegroups f
ON
i.groupid = f.data_space_id
where
f.name = 'fileGroupName1'
ORDER BY
p.used_page_count DESC;
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
October 13, 2012 at 8:58 am
sys.indexes, not sysindexes
Could have given incorrect results due to how things changed since SQL 2000
SELECT
schema_name(t.schema_id) as schemaName
,object_name(p.object_id) as table_name
,i.[name] AS index_name
,f.name as fileGroupName
,p.used_page_count
,p.row_count
,p.used_page_count * 8192 / (1024 * 1024) AS Size_MB
FROM
sys.dm_db_partition_stats p
INNER JOIN
sys.tables t
ON
p.object_id = t.object_id
LEFT OUTER JOIN
sys.indexes as i
ON
i.object_id = p.object_id
and i.index_id = p.index_id
INNER JOIN
sys.filegroups f
ON
i.data_space_id = f.data_space_id
where
f.name = 'fileGroupName1'
ORDER BY
p.used_page_count DESC;
How have you identified that there's 45GB of data on that 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
October 13, 2012 at 9:51 am
I use the "Disk Usage" report in SSMS to get total size and used space.
I found the reason.
This filegroup is used for a TEXT column in a table that itself is in another (smaller) filegroup.
I wonder if it makes sense to shrink this filegroup (where the TEXT col is defined);
I am not a fan of shrinking but have been asked to give it a go, as the file itself has 140 GB size but only 45 GB used space.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
October 13, 2012 at 9:54 am
You should be able to. As long as you just shrink the files with the TEXT it shouldn't even cause the usual fragmentation. Log pages are arranged differently than index pages
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
October 13, 2012 at 11:35 am
GilaMonster (10/13/2012)
You should be able to. As long as you just shrink the files with the TEXT it shouldn't even cause the usual fragmentation. Log pages are arranged differently than index pages
I'm having difficulty with the shrink; it gets stuck.
pls see my related post, posted also today:
http://www.sqlservercentral.com/Forums/Topic1372418-1550-1.aspx
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
October 13, 2012 at 2:25 pm
Very normal with LOBs, they do take huge amounts of time to move around. You just have to wait.
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
October 13, 2012 at 2:44 pm
GilaMonster (10/13/2012)
Very normal with LOBs, they do take huge amounts of time to move around. You just have to wait.
Thanks for the advice. 🙂
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply