ndf secondary data file - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

ndf secondary data file

Author

Topic

HarryAgain
Starting Member

4 Posts

Posted - 2010-09-03 : 06:50:51

How does one determine what table(s) are in an ndf file?
I have inherited a database with an ndf file but I have never used secondary data files before.
I have restored the database backup and opened it in Management studio.
But I don't know how to determine which tables are located in the ndf file.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-09-03 : 09:30:51

Here is one way..


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		
	i.data_space_id IN (SELECT data_space_id FROM sys.filegroups WHERE name = '<yourfilegroupname>')

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-03 : 09:50:39

[code]
SELECT tbl.name [table], fg.name [filegroup], sf.filename
FROM sys.tables AS tbl
INNER JOIN sys.indexes AS idx ON idx.object_id = tbl.object_id and idx.index_id < 2
INNER JOIN sys.partitions AS p ON p.object_id=CAST(tbl.object_id AS int) AND p.index_id=idx.index_id
INNER JOIN sys.indexes AS indx ON p.object_id = indx.object_id and p.index_id = indx.index_id
LEFT OUTER JOIN sys.destination_data_spaces AS dds ON dds.partition_scheme_id = indx.data_space_id and dds.destination_id = p.partition_number
LEFT OUTER JOIN sys.partition_schemes AS ps ON ps.data_space_id = indx.data_space_id
LEFT OUTER JOIN sys.partition_range_values AS prv ON prv.boundary_id = p.partition_number and prv.function_id = ps.function_id
LEFT OUTER JOIN sys.filegroups AS fg ON fg.data_space_id = dds.data_space_id or fg.data_space_id = indx.data_space_id
LEFT OUTER JOIN sys.partition_functions AS pf ON pf.function_id = prv.function_id
LEFT OUTER JOIN sys.sysfilegroups sfg on sfg.groupname = fg.name
LEFT OUTER JOIN sysfiles sf on sf.groupid = sfg.groupid
[/code]

Didn't see Rick already posted the answer. Coffee break

HarryAgain
Starting Member

4 Posts

Posted - 2010-09-03 : 11:37:24

Thanks RickD. Much appreciated.
I’m going to take a liberty and ask another two questions
There were two ndf files associated with this database.
One had no files associated with it at all. Would it be safe to assume that I can delete or drop this filegroup (ndf file)
The second ndf file had quite a few indexes associated with it. They all begin with the name fullText_index...
Does it make sense to hive off such indexes to a secondary data file. The overall database size is only about 20MB.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-09-08 : 04:44:39

I shouldn't be a problem as long as it is empty.

It can be a good idea to put your FTI's into another filegroup, it really depends on the situation.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources