Collections:
List All Files in a Filegroup
How to List All physical Files in a logical Filegroup?
✍: FYIcenter.com
In a large SQL Server Database, you may have multiple logical Filegroups.
And a Filegroup may have multiple physical Files.
To list all physical Files in a logical Filegroup, you need to join two system tables, sys.filegroups and sys.database_files, using the data_space_id and identifier.
Here is query to count the number of physical Files in each logical Filegroup:
select g.data_space_id, count(*) as count, g.name from sys.filegroups g, sys.database_files f where g.data_space_id = f.data_space_id group by g.data_space_id, g.name data_space_id count name 1 2 DEFAULT 2 1 PRODUCT 3 4 ORDER
Here is a query to list the physical Files of the first Filegroup:
select g.data_space_id, g.name, F.*
from sys.filegroups g, sys.database_files f
where g.data_space_id = f.data_space_id
and g.name = 'DEFAULT'
data_space_id name file_id type_desc name physical_name ...
1 DEFAULT 1 ROWS PRIMARY D:\DATA\primary.mdf ...
1 DEFAULT 2 ROWS EXTENSION D:\DATA\extension.ndf ...
2019-07-09, ∼4140🔥, 0💬
Popular Posts:
How to generate date and time test values? Timestamp values are frequently needed in testing almost ...
How to generate currency test values? Currency test values are frequently needed in testing date and...
What are date and time test values? Date and time test values are frequently needed in testing date ...
Where to find tutorials on UFT (Unified Functional Testing) tool? I want to know how to use UFT. Her...
How to turn off HTTP response compression in SoapUI? HTTP response compression reduces the data size...