What Is sys.database_files?


What Is sys.database_files view in SQL Server?

✍: FYIcenter.com


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

2019-07-14, 249👍, 0💬