January 25, 2010 at 8:51 am
Is there a way to find out which table is stored on which datafile?
Considering more than one datafile in each filegroup.
January 25, 2010 at 9:17 am
If the data files are all in the same file group, then no. Data could be spread among several data files.
January 25, 2010 at 9:19 am
Objects can't cross filegroups, but if you have multiple files in your filegroup, it would be on more than one.
January 25, 2010 at 9:22 am
You can find what filegroup the table is in by querying sys.partitions and sys.data_spaces.
If a table is on a filegroup with multiple files it'll be striped across all the files (unless the files were added after the last change to the table's data)
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
January 25, 2010 at 9:51 am
GilaMonster (1/25/2010)
You can find what filegroup the table is in by querying sys.partitions and sys.data_spaces.If a table is on a filegroup with multiple files it'll be striped across all the files (unless the files were added after the last change to the table's data)
Thank you all for quick response.
If I have added a new file in filegroup, new rows added to a table will again striped across al the files, right?
January 25, 2010 at 10:08 am
Yes
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply