| description | sys.master_files (Transact-SQL) | ||||
|---|---|---|---|---|---|
| title | sys.master_files (Transact-SQL) | Microsoft Docs | ||||
| ms.custom | |||||
| ms.date | 03/10/2016 | ||||
| ms.prod | sql | ||||
| ms.prod_service | database-engine, pdw | ||||
| ms.reviewer | |||||
| ms.technology | system-objects | ||||
| ms.topic | reference | ||||
| f1_keywords |
|
||||
| dev_langs |
|
||||
| helpviewer_keywords |
|
||||
| ms.assetid | 803b22f2-0016-436b-a561-ce6f023d6b6a | ||||
| author | rwestMSFT | ||||
| ms.author | randolphwest | ||||
| monikerRange | >=aps-pdw-2016||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current |
[!INCLUDE sql-asdbmi-pdw]
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_id | int | ID of the database to which this file applies. The masterdatabase_id is always 1. |
| file_id | int | ID of the file within database. The primary file_id is always 1. |
| file_guid | uniqueidentifier | Unique identifier of the file. NULL = Database was upgraded from an earlier version of [!INCLUDEssNoVersion] (Valid for SQL Server 2005 and earlier) . |
| type | tinyint | File type: 0 = Rows. 1 = Log 2 = FILESTREAM 3 = [!INCLUDEssInternalOnly] 4 = Full-text (Full-text catalogs earlier than [!INCLUDEssKatmai]; full-text catalogs that are upgraded to or created in [!INCLUDEssKatmai] or higher will report a file type 0.) |
| type_desc | nvarchar(60) | Description of the file type: ROWS LOG FILESTREAM FULLTEXT (Full-text catalogs earlier than [!INCLUDEssKatmai].) |
| data_space_id | int | ID of the data space to which this file belongs. Data space is a filegroup. 0 = Log files |
| name | sysname | Logical name of the file in the database. |
| physical_name | nvarchar(260) | Operating-system file name. |
| state | tinyint | File state: 0 = ONLINE 1 = RESTORING 2 = RECOVERING 3 = RECOVERY_PENDING 4 = SUSPECT 5 = [!INCLUDEssInternalOnly] 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 file size, in 8-KB pages. For a database snapshot, size reflects the maximum space that the snapshot can ever use for the file. Note: This field is populated as zero for FILESTREAM containers. Query the sys.database_files catalog view for the actual size of FILESTREAM containers. |
| max_size | int | Maximum file size, in 8-KB pages: 0 = No growth is allowed. -1 = File will grow until the disk is full. 268435456 = Log file will grow to a maximum size of 2 TB. Note: Databases that are upgraded with an unlimited log file size will report -1 for the maximum size of the log file. |
| growth | int | 0 = File is fixed size and will not grow. >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 is reusable. A log backup must be taken before the name (name or physical_name) can be reused for a new file name. 0 = File name is unavailable for reuse. |
| 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. |
| 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 container. |
| 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 container 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. |
| credential_id | int | The credential_id from sys.credentials used for storing the file. For example, when [!INCLUDEssNoVersion] 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. |
Note
When you drop or rebuild large indexes, or drop or truncate large tables, the [!INCLUDEssDE] 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.master_files immediately after dropping or truncating a large object may not reflect the actual disk space available.
Note
For tempdb, view sys.master_files shows initial tempdb size. The values are used as a template for tempdb creation at startup of SQL Server. So, when tempdb grows it is not reflected in the view. To get current size of tempdb files, query tempdb.sys.database_files.
The minimum permissions that are required to see the corresponding row are CREATE DATABASE, ALTER ANY DATABASE, or VIEW ANY DEFINITION.
Databases and Files Catalog Views (Transact-SQL)
File States
sys.databases (Transact-SQL)
sys.database_files (Transact-SQL)
Database Files and Filegroups