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, 2051🔥, 0💬
Popular Posts:
How to use HTTP protocol to post an XML message to a server? You can follow this tutorial to post an...
How to generate test phone numbers for US and Canada? Test phone numbers are frequently needed in te...
Where to find tutorials on Apache JMeter test tool? I want to know how to use Apache JMeter. Here is...
How to convert hexadecimal encoded data back to binary data (or Hex to Binary Decoding)? Hex to Bina...
How to generate currency test values? Currency test values are frequently needed in testing date and...