sys.master_files (Transact-SQL)
Applies to: 
SQL Server
Azure SQL Managed Instance
Analytics Platform System (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 SQL Server (Valid for SQL Server 2005 and earlier) . |
| 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 (Full-text catalogs earlier than SQL Server 2008 (10.0.x); full-text catalogs that are upgraded to or created in SQL Server 2008 (10.0.x) 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 SQL Server 2008 (10.0.x).) |
| 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 = 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 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 SQL 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. |
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.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.
Permissions
The minimum permissions that are required to see the corresponding row are CREATE DATABASE, ALTER ANY DATABASE, or VIEW ANY DEFINITION.
See Also
Databases and Files Catalog Views (Transact-SQL)
File States
sys.databases (Transact-SQL)
sys.database_files (Transact-SQL)
Database Files and Filegroups