April 29, 2011 at 8:49 am
I'm not sure if this particular question has been addressed for this particular situation before.
We have a SQL 2008 DB where in the past, we added an additional file to the PRIMARY filegroup in order to improve performance on some large tables. Recently, we dramatically reduced the amount of data stored in this database and are comfortable having one file in this group (there is no additional FILEGROUP whatsoever).
What I'd like to know is to find out what tables are actually stored on this second file. Running 'sp_help TABLENAME' only gives in the index portion of the output, that the index is located on PRIMARY, and data_located_on_filegroup is PRIMARY. What I'd like to know is if there is a way to dig further down and see what actual file the table resides on before I run:
[font="Courier New"]DBCC SHRINKFILE (N'FILE2_ON_PRIMARY_FILEGROUP', EMPTYFILE)[/font]
which moves all data from this file back to the original file in PRIMARY. I want to make sure it won't take too long, otherwise a longer maintenance window may be necessary. I have a sneaky suspicision this may not be stored in a DMV (I hunted through sys.indexes and sys.filegroups as well as a few others, but may have missed one).
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
April 29, 2011 at 9:20 am
Tables and indexes are stored in filegroups not in files, so a table will usually be distributed across all files in a filegroup.
April 29, 2011 at 9:22 am
Not sure if this is what you want....
select 'table_name'=object_name(i.id)
,i.indid
,'index_name'=i.name
,i.groupid
,'filegroup'=f.name
,'file_name'=d.physical_name
,'dataspace'=s.name
from sys.sysindexes i
,sys.filegroups f
,sys.database_files d
,sys.data_spaces s
where objectproperty(i.id,'IsUserTable') = 1
and f.data_space_id = i.groupid
and f.data_space_id = d.data_space_id
and f.data_space_id = s.data_space_id
order by f.name,object_name(i.id),groupid
April 29, 2011 at 10:07 am
SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]
FROM sys.indexes i
INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id]
WHERE i.data_space_id = f.data_space_id
AND o.type = 'U' -- User Created Tables
GO
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 29, 2011 at 10:19 am
As Michael said, tables are normally spread across the files in a filegroup. If you want to figure out which data has gone to which file in the filegroup, you can look into using the undocumented function in SQL 2008 - sys.fn_PhysLocCracker.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 29, 2011 at 11:25 am
cheshirefox (4/29/2011)
Not sure if this is what you want....select 'table_name'=object_name(i.id)
,i.indid
,'index_name'=i.name
,i.groupid
,'filegroup'=f.name
,'file_name'=d.physical_name
,'dataspace'=s.name
from sys.sysindexes i
,sys.filegroups f
,sys.database_files d
,sys.data_spaces s
where objectproperty(i.id,'IsUserTable') = 1
and f.data_space_id = i.groupid
and f.data_space_id = d.data_space_id
and f.data_space_id = s.data_space_id
order by f.name,object_name(i.id),groupid
Thanks CheshireFox...This helps a lot, very nice. I'm going to tweak it a bit to see if I can get size of usage as well which may work with the function below...
SQLRNNR, the function and it's analogue sys.fn_physLocFormatter definitely drill down into the detail. Hmmm...maybe I can combine the two and get some very interesting info. 🙂 For that function support, I found it here, on Paul Randal's blog.
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
April 29, 2011 at 11:40 am
GabyYYZ (4/29/2011)
SQLRNNR, the function and it's analogue sys.fn_physLocFormatter definitely drill down into the detail. Hmmm...maybe I can combine the two and get some very interesting info. 🙂 For that function support, I found it here, on Paul Randal's blog.
That is a good resource. I think it would be interesting to combine the two to find more info.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply