July 8, 2011 at 12:09 pm
Guys,
Is there anyway to locate what tables belong database files (assuming that there are multiple database files mdf and ndf). I used this query below, but it only tells me database files and does not correspond to the database objects.
SELECT *
FROM master.sys.master_files
WHERE database_id = 35
Any suggestions/inputs would help
Thanks
July 8, 2011 at 12:15 pm
Are they all in the same filegroup or are they in separate filegroups?
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
July 8, 2011 at 12:38 pm
am-244616 (7/8/2011)
Is there anyway to locate what tables belong database files (assuming that there are multiple database files mdf and ndf). I used this query below, but it only tells me database files and does not correspond to the database objects.SELECT *
FROM master.sys.master_files
WHERE database_id = 35
Any suggestions/inputs would help
The more granular level you can get in regards to knowing where a particular object resides is the filegroup; filegroups are a collection of datafiles.
Query below shows objects for a particular filegroup...
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 = 2
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 11, 2011 at 1:15 am
we use the followin script to find out which objects are on which filegroup
SELECT
o.[name] AS ObjectName,
o.[type] AS ObjectType,
s.[name] AS SchemaName,
f.[name] AS [Filename],
i.[name] AS PrimaryKey,
i.[index_id] AS IndexID
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]
INNER JOIN
sys.schemas s
ON
s.[schema_id] = o.[schema_id]
order by
s.[name],
o.[name]
As you can have multiple files per file group e.g 3 files in FG1, SQL will distribute the table across all three files, it wont all be stored on one file inside that filegroup. So to pin it back to one file is not possible, you can only pin it back to a filegroup.
If you only have one file per filegroup then you will be ok.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply