What Is sys.database_files view in SQL Server?

sys.database_files is a system view in a SQL Server Database that contains information about each physical files used by the database.

Each record in the sys.database_files table represents a single SQL Server Physical File with the following key fields:

• file_id - int: ID of the file within database.
• file_guid - uniqueidentifier: GUID for the file.
• type - tinyint: File type.
• type_desc - nvarchar(60): Description of the file type.
• data_space_id - int: Value can be 0 or greater than 0. A value of 0 represents the database log file, and a value greater than 0 represents the ID of the filegroup where this data file is stored.
• name - sysname: Logical name of the physical file in the database.
• physical_name - nvarchar(260): Operating-system file name. If the database is hosted by an AlwaysOn readable secondary replica, physical_name indicates the file location of the primary replica database. For the correct file location of a readable secondary database, query sys.sysaltfiles.
• state - tinyint: File state.
• state_desc - nvarchar(60): Description of the file state.
• size - int: Current size of the file, in 8-KB pages.
• max_size - int: Maximum file size, in 8-KB pages.

Here is an example of SQL Server Database with only 2 required physical files:

SELECT * FROM sys.database_files

file_id  type  type_desc  data_space_id  name     physical_name        ...
1        0     ROWS       1              PRIMARY  D:\DATA\primary.mdf  ...
2        1     LOG        0              LOG      D:\DATA\log.ldf      ...

⇒ What Is a SQL Server Filegroup

⇐ What Is a SQL Server File

⇑ SQL Server System Views and Functions

⇑⇑ SQL Server Storage Tutorials