Bewerken

Delen via

sys.master_files (Transact-SQL)

  • Artikel

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. sys.master_files is a single, system-wide view.

Column name Data type Description
database_id int ID of the database to which this file applies. The database_id for the master database 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 (9.x) and earlier versions).
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) and later versions 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:

-1 = File grows until the disk is full.

268435456 = Log file grows to a maximum size of 2 TB.

Note: Databases upgraded with an unlimited log file size report -1 for the maximum size of the log file.

Note: If max_size = -1 and growth = 0, then no growth is allowed.
growth int 0 = File is fixed size and doesn't grow.

> 0 = File grows 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 isn't 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 are included in a differential backup.
differential_base_guid uniqueidentifier Unique identifier of the base backup on which a differential backup is 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.

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.

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.

Remarks

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 don't release allocated space immediately. Therefore, the values returned by sys.master_files immediately after dropping or truncating a large object might not reflect the actual disk space available.

For the tempdb database, sys.master_files shows the initial tempdb size. The values are used as a template for tempdb creation at startup of SQL Server. tempdb growth isn't reflected in this view. To get the current size of tempdb files, query tempdb.sys.database_files instead.

Permissions

The minimum permissions that are required to see the corresponding row are CREATE DATABASE, ALTER ANY DATABASE, or VIEW ANY DEFINITION.

Related content