sys.database_files (Transact-SQL)sys.database_files (Transact-SQL)

本主题适用于:yesSQL Server(从 2008 开始)yesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

每个存储在数据库本身中的数据库文件在表中占用一行。Contains a row per file of a database as stored in the database itself. 这是一个基于每个数据库的视图。This is a per-database view.

列名Column name 数据类型Data type DescriptionDescription
file_idfile_id intint 数据库内文件的 ID。ID of the file within database.
file_guidfile_guid uniqueidentifieruniqueidentifier 文件的 GUID。GUID for the file.

NULL = 数据库是从早期版本的 SQL ServerSQL Server 升级的。NULL = Database was upgraded from an earlier version of SQL ServerSQL Server.
类型type tinyinttinyint 文件类型:File type:

0 = 行(包括升级到的或在 SQL Server 2017SQL Server 2017 中创建的全文目录的文件。)0 = Rows (Includes files of full-text catalogs that are upgraded to or created in SQL Server 2017SQL Server 2017.)

1 = 日志1 = Log

2 = FILESTREAM2 = FILESTREAM

3 = 标识为仅供参考。Identified for informational purposes only. 不提供支持。Not supported. 不保证以后的兼容性。Future compatibility is not guaranteed.3 = 标识为仅供参考。Identified for informational purposes only. 不提供支持。Not supported. 不保证以后的兼容性。Future compatibility is not guaranteed.

4 = 全文( SQL Server 2017SQL Server 2017之前的全文目录;升级到的或在 SQL Server 2017SQL Server 2017 中创建的全文目录将报告文件类型 0。)4 = Full-text (Full-text catalogs earlier than SQL Server 2017SQL Server 2017; full-text catalogs that are upgraded to or created in SQL Server 2017SQL Server 2017 will report a file type 0.)
type_desctype_desc nvarchar(60)nvarchar(60) 文件类型的说明:Description of the file type:

ROWS(包括升级到的或在 SQL Server 2017SQL Server 2017中创建的全文目录的文件)。ROWS (Includes files of full-text catalogs that are upgraded to or created in SQL Server 2017SQL Server 2017.)

LOGLOG

FILESTREAMFILESTREAM

FULLTEXT( SQL Server 2017SQL Server 2017 之前的全文目录。)FULLTEXT (Full-text catalogs earlier than SQL Server 2017SQL Server 2017.)
data_space_iddata_space_id intint 该值可以是 0 或大于 0。Value can be 0 or greater than 0. 值为 0 表示数据库日志文件,值大于 0 表示存储此数据文件的文件组的 ID。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 sysnamesysname 数据库中文件的逻辑名称。Logical name of the file in the database.
physical_namephysical_name nvarchar(260)nvarchar(260) 操作系统文件名。Operating-system file name. 如果数据库承载由某一 AlwaysOn可读辅助副本physical_name指示主副本数据库的文件位置。If the database is hosted by an AlwaysOn readable secondary replica, physical_name indicates the file location of the primary replica database. 对于正确的文件位置的可读辅助数据库,查询sys.sysaltfilesFor the correct file location of a readable secondary database, query sys.sysaltfiles.
Statestate tinyinttinyint 文件状态:File state:

0 = ONLINE 0 = ONLINE

1 = RESTORING1 = RESTORING

2 = RECOVERING2 = RECOVERING

3 = RECOVERY_PENDING3 = RECOVERY_PENDING

4 = SUSPECT 4 = SUSPECT

5 = 标识为仅供参考。Identified for informational purposes only. 不提供支持。Not supported. 不保证以后的兼容性。Future compatibility is not guaranteed.5 = 标识为仅供参考。Identified for informational purposes only. 不提供支持。Not supported. 不保证以后的兼容性。Future compatibility is not guaranteed.

6 = OFFLINE 6 = OFFLINE

7 = DEFUNCT7 = DEFUNCT
state_descstate_desc nvarchar(60)nvarchar(60) 文件状态的说明:Description of the file state:

ONLINEONLINE

RESTORINGRESTORING

RECOVERINGRECOVERING

RECOVERY_PENDINGRECOVERY_PENDING

SUSPECTSUSPECT

OFFLINEOFFLINE

DEFUNCTDEFUNCT

有关详细信息,请参阅文件状态For more information, see File States.
sizesize intint 文件的当前大小(以 8 KB 页为单位)。Current size of the file, in 8-KB pages.

0 = 不适用0 = Not applicable

对于数据库快照来说,size 表示该快照可以一直用于文件的最大空间。For a database snapshot, size reflects the maximum space that the snapshot can ever use for the file.

FILESTREAM 文件组容器大小反映当前使用的容器的大小。For FILESTREAM filegroup containers, size reflects the current used size of the container.
max_sizemax_size intint 最大文件大小(以 8 KB 为单位的页数):Maximum file size, in 8-KB pages:

0 = 不允许增长。0 = No growth is allowed.

-1 = 文件将一直增长到磁盘充满为止。-1 = File will grow until the disk is full.

268435456 = 日志文件将增长到最大大小 2 TB。268435456 = Log file will grow to a maximum size of 2 TB.

对于 FILESTREAM 文件组容器,max_size 反映容器的最大大小。For FILESTREAM filegroup containers, max_size reflects the maximum size of the container.

请注意不受限制的日志文件大小与升级的数据库将报告为-1 的日志文件的最大大小。Note that databases that are upgraded with an unlimited log file size will report -1 for the maximum size of the log file.
增长growth intint 0 = 文件大小固定,不会增长。0 = File is fixed size and will not grow.

>0 = 文件将自动增长。>0 = File will grow automatically.

is_percent_growth = 0,则增量以 8 KB 页为单位,并舍入到最近的 64 KB。If is_percent_growth = 0, growth increment is in units of 8-KB pages, rounded to the nearest 64 KB.

如果 is_percent_growth = 1,增量将用整数百分比表示。If is_percent_growth = 1, growth increment is expressed as a whole number percentage.
is_media_read_onlyis_media_read_only bitbit 1 = 文件位于只读介质上。1 = File is on read-only media.

0 = 文件位于读写介质上。0 = File is on read-write media.
is_read_onlyis_read_only bitbit 1 = 文件标记为只读。1 = File is marked read-only.

0 = 文件标记为读/写。0 = File is marked read/write.
is_sparseis_sparse bitbit 1 = 文件是稀疏文件。1 = File is a sparse file.

0 = 文件不是稀疏文件。0 = File is not a sparse file.

有关详细信息,请参阅查看数据库快照的稀疏文件大小 (Transact-SQL)For more information, see View the Size of the Sparse File of a Database Snapshot (Transact-SQL).
is_percent_growthis_percent_growth bitbit 1 = 文件的增长以百分比表示。1 = Growth of the file is a percentage.

0 = 以页数为单位表示绝对增长大小。0 = Absolute growth size in pages.
is_name_reservedis_name_reserved bitbit 1 = 只有在下一次日志备份之后才能重用删除的文件名(name 或 physical_name)。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_lsncreate_lsn numeric(25,0)numeric(25,0) 文件创建时的日志序列号 (LSN)。Log sequence number (LSN) at which the file was created.
drop_lsndrop_lsn numeric(25,0)numeric(25,0) 文件删除时的 LSN。LSN at which the file was dropped.

0 = 文件名无法重用。0 = The file name is unavailable for reuse.
read_only_lsnread_only_lsn numeric(25,0)numeric(25,0) 包含该文件的文件组从可读/写更改为只读(最新更改)时的 LSN。LSN at which the filegroup that contains the file changed from read/write to read-only (most recent change).
read_write_lsnread_write_lsn numeric(25,0)numeric(25,0) 包含该文件的文件组从只读更改为可读/写(最新更改)时的 LSN。LSN at which the filegroup that contains the file changed from read-only to read/write (most recent change).
differential_base_lsndifferential_base_lsn numeric(25,0)numeric(25,0) 差异备份的基准。Base for differential backups. 在此 LSN 之后更改的数据区将包含在差异备份中。Data extents changed after this LSN will be included in a differential backup.
differential_base_guiddifferential_base_guid uniqueidentifieruniqueidentifier 差异备份所基于的基准备份的唯一标识符。Unique identifier of the base backup on which a differential backup will be based.
differential_base_timedifferential_base_time datetimedatetime 与 differential_base_lsn 相对应的时间。Time corresponding to differential_base_lsn.
redo_start_lsnredo_start_lsn numeric(25,0)numeric(25,0) 下一次前滚必须开始时的 LSN。LSN at which the next roll forward must start.

除非 state = RESTORING 或 state = RECOVERY_PENDING,否则为 NULL。Is NULL unless state = RESTORING or state = RECOVERY_PENDING.
redo_start_fork_guidredo_start_fork_guid uniqueidentifieruniqueidentifier 恢复分叉的唯一标识符。Unique identifier of the recovery fork. 还原的下一个日志备份的 first_fork_guid 必须与此值匹配。The first_fork_guid of the next log backup restored must match this value. 这表示文件的当前状态。This represents the current state of the file.
redo_target_lsnredo_target_lsn numeric(25,0)numeric(25,0) 对此文件的联机前滚可以停止时的 LSN。LSN at which the online roll forward on this file can stop.

除非 state = RESTORING 或 state = RECOVERY_PENDING,否则为 NULL。Is NULL unless state = RESTORING or state = RECOVERY_PENDING.
redo_target_fork_guidredo_target_fork_guid uniqueidentifieruniqueidentifier 可以在其上恢复文件的恢复分叉。The recovery fork on which the file can be recovered. 与 redo_target_lsn 成对使用。Paired with redo_target_lsn.
backup_lsnbackup_lsn numeric(25,0)numeric(25,0) 文件的最新数据或差异备份的 LSN。The LSN of the most recent data or differential backup of the file.

备注

在删除或重新生成大型索引时,或者在删除或截断大型表时, 数据库引擎Database Engine将延迟实际页释放及其关联锁,直至事务提交完毕为止。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. 因此,在删除或截断大型对象后由 sys.database_files 立即返回的值可能不反映实际可用的磁盘空间。Therefore, the values returned by sys.database_files immediately after dropping or truncating a large object may not reflect the actual disk space available.

PermissionsPermissions

要求 公共 角色具有成员身份。Requires membership in the public role. 有关详细信息,请参阅 Metadata Visibility ConfigurationFor 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;

有关详细信息时使用 SQL DatabaseSQL Database,请参阅Azure SQL 数据库 V12 中确定数据库大小SQL 客户顾问团队博客上。For more information when using SQL DatabaseSQL Database, see Determining Database Size in Azure SQL Database V12 on the SQL Customer Advisory Team blog.

请参阅See Also

数据库和文件目录视图 (Transact-SQL) Databases and Files Catalog Views (Transact-SQL)
文件状态 File States
sys.databases (Transact-SQL) sys.databases (Transact-SQL)
sys.master_files (Transact-SQL) sys.master_files (Transact-SQL)
Database Files and Filegroups Database Files and Filegroups
sys.data_spaces (Transact-SQL)sys.data_spaces (Transact-SQL)