Edit

Share via

sys.database_files (Transact-SQL)

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Contains a row per file of a database as stored in the database itself. This is a per-database view.

Column name Data type Description
file_id int ID of the file within database.
file_guid uniqueidentifier GUID for the file.

NULL = Database was upgraded from an earlier version of SQL Server (Valid for SQL Server 2005 and earlier versions).
type tinyint File type:

0 = Rows
1 = Log
2 = FILESTREAM
3 = Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
4 = Full-text
type_desc nvarchar(60) Description of the file type:

ROWS
LOG
FILESTREAM
FULLTEXT
data_space_id int Value can be zero or greater than zero. A value of 0 represents the database log file, and a value greater than zero 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 availability group 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:

0 = ONLINE
1 = RESTORING
2 = RECOVERING
3 = RECOVERY_PENDING
4 = SUSPECT
5 = Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
6 = OFFLINE
7 = DEFUNCT
state_desc nvarchar(60) Description of the file state:

ONLINE
RESTORING
RECOVERING
RECOVERY_PENDING
SUSPECT
OFFLINE
DEFUNCT
For more information, see File States.
size int Current size of the file, in 8-KB pages.

0 = Not applicable
For a database snapshot, size reflects the maximum space that the snapshot can ever use for the file.
For FILESTREAM filegroup containers, size reflects the current used size of the container.
max_size int Maximum file size, in 8-KB pages:

0 = No growth is allowed.
-1 = File can grow until the disk is full.
268435456 = Log file can grow to a maximum size of 2 TB.
For FILESTREAM filegroup containers, max_size reflects the maximum size of the container.
Databases that are upgraded with an unlimited log file size report -1 for the maximum size of the log file.
In Azure SQL Database, the sum of max_size values for all data files can be less than the maximum data size for the database. Use DATABASEPROPERTYEX(DB_NAME(), 'MaxSizeInBytes') to determine maximum data size.
growth int 0 = File is fixed size and will not grow.

Greater than 0 = File will grow automatically.
If is_percent_growth = 0, growth increment is in units of 8-KB pages, rounded to the nearest 64 KB.
If is_percent_growth = 1, growth increment is expressed as a whole number percentage.
is_media_read_only bit 1 = File is on read-only media.

0 = File is on read-write media.
is_read_only bit 1 = File is marked read-only.

0 = File is marked read/write.
is_sparse bit 1 = File is a sparse file.

0 = File is not a sparse file.
For more information, see View the Size of the Sparse File of a Database Snapshot (Transact-SQL).
is_percent_growth bit 1 = Growth of the file is a percentage.

0 = Absolute growth size in pages.
is_name_reserved bit 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 might not reflect the actual disk space available.

Permissions

Requires membership in the public role. For more information, see Metadata Visibility Configuration.

Examples

The following statement returns the name, file size, and the amount of empty space for each database file.

SELECT name, size/128.0 FileSizeInMB,
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 
   AS EmptySpaceInMB
FROM sys.database_files;

Find example queries using SQL Database, in Manage file space for databases in Azure SQL Database. You can:

Related content