4 = Full-text (Full-text catalogs earlier than SQL Server 2017; full-text catalogs that are upgraded to or created in SQL Server 2017 will report a file type 0.)

type_desc

nvarchar(60)

Description of the file type:

ROWS (Includes files of full-text catalogs that are upgraded to or created in SQL Server 2017.)

LOG

FILESTREAM

FULLTEXT (Full-text catalogs earlier than SQL Server 2017.)

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 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.

1 = Dropped file name (name or physical_name) is reusable only after the next log backup. When files are dropped from a database, the logical names stay in a reserved state until the next log backup. This column is relevant only under the full recovery model and the bulk-logged recovery model.

create_lsn

numeric(25,0)

Log sequence number (LSN) at which the file was created.

drop_lsn

numeric(25,0)

LSN at which the file was dropped.

0 = The file name is unavailable for reuse.

read_only_lsn

numeric(25,0)

LSN at which the filegroup that contains the file changed from read/write to read-only (most recent change).

read_write_lsn

numeric(25,0)

LSN at which the filegroup that contains the file changed from read-only to read/write (most recent change).

differential_base_lsn

numeric(25,0)

Base for differential backups. Data extents changed after this LSN will be included in a differential backup.

differential_base_guid

uniqueidentifier

Unique identifier of the base backup on which a differential backup will be based.

differential_base_time

datetime

Time corresponding to differential_base_lsn.

redo_start_lsn

numeric(25,0)

LSN at which the next roll forward must start.

Is NULL unless state = RESTORING or state = RECOVERY_PENDING.

redo_start_fork_guid

uniqueidentifier

Unique identifier of the recovery fork. The first_fork_guid of the next log backup restored must match this value. This represents the current state of the file.

redo_target_lsn

numeric(25,0)

LSN at which the online roll forward on this file can stop.

Is NULL unless state = RESTORING or state = RECOVERY_PENDING.

redo_target_fork_guid

uniqueidentifier

The recovery fork on which the file can be recovered. Paired with redo_target_lsn.

backup_lsn

numeric(25,0)

The LSN of the most recent data or differential backup of the file.

Note

When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. Deferred drop operations do not release allocated space immediately. Therefore, the values returned by sys.database_files immediately after dropping or truncating a large object may not reflect the actual disk space available.