Skip to content

sys-master-files-transact-sql.md

Latest commit

 

History

History
78 lines (68 loc) · 8.38 KB

sys-master-files-transact-sql.md

File metadata and controls

78 lines (68 loc) · 8.38 KB
title sys.master_files (Transact-SQL)
description The sys.master_files system catalog view contains a row per file of a database as stored in the master database.
author rwestMSFT
ms.author randolphwest
ms.date 11/25/2023
ms.service sql
ms.subservice system-objects
ms.topic reference
f1_keywords
sys.master_files
master_files_TSQL
sys.master_files_TSQL
master_files
helpviewer_keywords
sys.master_files catalog view
dev_langs
TSQL
monikerRange >=aps-pdw-2016 || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current

sys.master_files (Transact-SQL)

[!INCLUDE sql-asdbmi-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 [!INCLUDE ssNoVersion] (Valid for [!INCLUDE ssversion2005-md] and earlier versions).
type tinyint File type:

0 = Rows

1 = Log

2 = FILESTREAM

3 = [!INCLUDE ssInternalOnly]

4 = Full-text (Full-text catalogs earlier than [!INCLUDE sql2008-md]; full-text catalogs that are upgraded to or created in [!INCLUDE sql2008-md] 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 [!INCLUDE sql2008-md].)
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 = [!INCLUDE ssInternalOnly]

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.
is_persistent_log_buffer bit 1 = The log file is a persistent log buffer.

0 = The file is not a persistent log buffer.

For more information, see Add persistent log buffer to a database.
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 [!INCLUDE ssNoVersion] 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 [!INCLUDE ssDE] 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 [!INCLUDE ssnoversion-md]. 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