To inspect files in SQL Server, run a query against sys.database_files while connected to the database you want to check. Join its data_space_id to sys.filegroups to show each data file’s filegroup; log files have no filegroup.
Query file and filegroup details for the current database
Run this in the database you want to inspect. sys.database_files returns one row per database file, including logical and physical names, type, state, size, growth, and data-space identifier. The LEFT JOIN keeps log-file rows in the results even though they do not belong to a filegroup.
SELECT
df.file_id,
df.name AS logical_file_name,
df.type_desc,
df.physical_name,
fg.name AS filegroup_name,
df.state_desc,
df.size / 128.0 AS size_mb,
df.max_size,
df.growth
FROM sys.database_files AS df
LEFT JOIN sys.filegroups AS fg
ON df.data_space_id = fg.data_space_id;Microsoft documents sys.database_files and sys.filegroups as catalog views for database file and filegroup metadata. A positive data_space_id identifies a data file’s filegroup; 0 identifies a log file.
Interpret size, growth, and maximum-size values
The size value in sys.database_files is expressed in 8-KB pages. Dividing it by 128 converts pages to megabytes, as in the query above. For unused space inside a database file, Microsoft’s example uses FILEPROPERTY(name, 'SpaceUsed') to compare used pages with the file’s total size. This is space within the database file, not a measurement of free space on the operating-system disk.
#1 Best Overall
max_size = -1means the file can grow until the disk is full.growth = 0means the file is fixed in size.- Check the documentation for the unit and interpretation of growth values before comparing or reporting them; the catalog can represent growth in pages or as a percentage, depending on the setting.
The physical_name field is the path recorded in SQL Server metadata. It is not a disk-health check, and its interpretation can vary with platform or replica context.
Use built-in reports instead of writing a query
For a quick report in the target database, run one of these system stored procedures:
EXEC sys.sp_helpfile;reports the current database’s files and their properties. See Microsoft’ssp_helpfilereference.EXEC sys.sp_helpfilegroup;reports filegroup names and attributes. Pass a filegroup name to list that group’s files and file properties. See Microsoft’ssp_helpfilegroupreference.
The catalog-view query is more flexible when you need selected columns or a joined result. The procedures are convenient when a built-in report is enough. These are per-database approaches, not a single server-wide inventory.
Understand what filegroups tell you
Filegroups organize data files for allocation and administration; transaction log files are not members of filegroups. The primary filegroup contains the primary data file and any secondary files not assigned to another group. User-defined filegroups let administrators group and place data files for management purposes.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Rank #3
SQL Server uses proportional fill when allocating data among files in a filegroup, taking their available free space into account. That behavior does not mean that adding files automatically improves performance for every workload. Microsoft’s Database Files and Filegroups documentation advises that “Most databases will work well with a single data file and a single transaction log file.”
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Permissions and metadata visibility
Microsoft documents sys.database_files and sys.filegroups as visible to the public role, and says the referenced stored procedures require public-role membership. Catalog metadata visibility rules still affect which metadata a principal can see. Check the permissions and configuration of the specific SQL Server deployment rather than assuming every account will return identical results; see Microsoft’s metadata visibility documentation.
Quick Recap
Best Value
Rank #4
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
