sys.master_files (Transact-SQL)sys.master_files (Transact-SQL)

适用对象:是SQL Server 否Azure SQL 数据库 否Azure SQL 数据仓库 是并行数据仓库 APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse yesParallel Data Warehouse

master 数据库中的每个文件对应一行。Contains a row per file of a database as stored in the master database. 这是一个系统范围视图。This is a single, system-wide view.

列名Column name 数据类型Data type 描述Description
database_iddatabase_id intint 应用此文件的数据库的 ID。ID of the database to which this file applies. Masterdatabase_id 始终为1。The masterdatabase_id is always 1.
file_idfile_id intint 数据库内文件的 ID。ID of the file within database. 主 file_id 始终为 1。The primary file_id is always 1.
file_guidfile_guid uniqueidentifieruniqueidentifier 文件的唯一标识符。Unique identifier of the file.

NULL = 从 @no__t 的早期版本升级了数据库(对 SQL Server 2005 及更早版本有效)。NULL = Database was upgraded from an earlier version of SQL ServerSQL Server (Valid for SQL Server 2005 and earlier) .
typetype tinyinttinyint 文件类型:File type:

0 = 行。0 = Rows.

1 = 日志1 = Log


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




FULLTEXT(SQL Server 2008SQL Server 2008 之前的全文目录。)FULLTEXT (Full-text catalogs earlier than SQL Server 2008SQL Server 2008.)
data_space_iddata_space_id intint 此文件所属数据空间的 ID。ID of the data space to which this file belongs. 数据空间是一个文件组。Data space is a filegroup.

0 = 日志文件0 = Log files
namename sysnamesysname 数据库中文件的逻辑名称。Logical name of the file in the database.
physical_namephysical_name nvarchar(260)nvarchar(260) 操作系统文件名。Operating-system file name.
statestate tinyinttinyint 文件状态:File state:






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.


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








有关详细信息,请参阅文件状态For more information, see File States.
大小size intint 当前文件大小(以 8 KB 为单位的页数)。Current file size, in 8-KB pages. 对于数据库快照来说,size 表示该快照可以一直用于文件的最大空间。For a database snapshot, size reflects the maximum space that the snapshot can ever use for the file.

注意:对于 FILESTREAM 容器,此字段填充为零。Note: This field is populated as zero for FILESTREAM containers. 查询database_files目录视图以了解 FILESTREAM 容器的实际大小。Query the sys.database_files catalog view for the actual size of FILESTREAM containers.
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.

注意:使用无限制日志文件大小升级的数据库将报告-1,以表示日志文件的最大大小。Note: Databases that are upgraded with an unlimited log file size will report -1 for the maximum size of the log file.
growthgrowth 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_onlyFis_media_read_onlyF 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 = 可重用已删除的文件名。1 = Dropped file name is reusable. 必须进行日志备份后才能将该名称(name 或 physical_name)重新用于新建文件名。A log backup must be taken before the name (name or physical_name) can be reused for a new file name.

0 = 文件名不可重复使用。0 = File name is unavailable for reuse.
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.
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 container.
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 container 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.
credential_idcredential_id intint 用于存储文件的 @no__t 中的 @no__t 0。The credential_id from sys.credentials used for storing the file. 例如,如果在 Azure 虚拟机上运行 SQL ServerSQL Server,并且数据库文件存储在 Azure blob 存储中,则使用存储位置的访问凭据配置凭据。For example, when SQL ServerSQL Server is running on an Azure Virtual Machine and the database files are stored in Azure blob storage, a credential is configured with the access credentials to the storage location.


在删除或重新生成大型索引时,或者在删除或截断大型表时,数据库引擎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.master_files 返回的值在删除或截断了大型对象后,可能无法立即反映出磁盘的实际可用空间。Therefore, the values returned by sys.master_files immediately after dropping or truncating a large object may not reflect the actual disk space available.


查看相应行所必需的最低权限是 CREATE DATABASE、ALTER ANY DATABASE 或 VIEW ANY DEFINITION。The minimum permissions that are required to see the corresponding row are CREATE DATABASE, ALTER ANY DATABASE, or VIEW ANY DEFINITION.

请参阅See Also

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