May 25, 2017 at 11:28 am
Hello,
I have an issue quit strange.
i got 2 file groups:
1. primary
2.secondary
The .NDF file of secondary filegroup size is 12767 MB.
i cant see what tables on this file group...
if i run the query below i get no results.
Please help me!!!
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 = 2 -- My secondary Filegroup ID
GO
May 25, 2017 at 2:35 pm
Are any of your tables partitioned? The tables on the secondary filegroup could be partitioned, so the index is related to a partition scheme not a specific filegroup, you could see that using sys.data_spaces instead of sys.filegroups, but they will have a different data_space_id than the secondary filegroup so you'd have to remove the WHERE clause condition data_space_id = 2, and instead use sys.destination_data_spaces to translate the partition_number to the true filegroup
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-destination-data-spaces-transact-sql
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply