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

適用於: 是SQL Server 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure 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 描述Description
file_idfile_id intint 資料庫內的檔案識別碼。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.
typetype 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 的值則代表儲存這個資料檔之檔案群組的識別碼。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.
namename 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 = ONLINE0 = ONLINE

1 = RESTORING1 = RESTORING

2 = RECOVERING2 = RECOVERING

3 = RECOVERY_PENDING3 = RECOVERY_PENDING

4 = SUSPECT4 = 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 = OFFLINE6 = 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 EngineDatabase Engine 會延遲取消配置實際的頁面及其相關聯鎖定,直到認可交易之後。When you drop or rebuild large indexes, or drop or truncate large tables, the Database EngineDatabase 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

需要 public 角色的成員資格。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 Database 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)