sys.master_files (Transact-SQL)

Contains a row per file of a database as stored in the master database. This is a single, system-wide view. The minimum permissions required to see the corresponding row are CREATE DATABASE, ALTER ANY DATABASE, or VIEW ANY DEFINITION.

Column name

Data type

Description

database_id

int

ID of the database to which this file applies.

file_id

int

ID of the file within database.

file_guid

uniqueidentifier

Unique identifier of the file.

NULL = Database was upgraded from an earlier version of Microsoft SQL Server.

type

tinyint

File type:

0 = Rows

1 = Log

2 = Reserved for future use.

3 = Reserved for future use.

4 = Full-text

type_desc

nvarchar(60)

Description of the file type:

ROWS

LOG

FULLTEXT

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 = Reserved for future use.

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.

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 Understanding Sparse File Sizes in Database Snapshots.

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.

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. For more information about deferred allocations, see Dropping and Rebuilding Large Objects.

See Also

Reference

Databases and Files Catalog Views (Transact-SQL)
sys.databases (Transact-SQL)
sys.database_files (Transact-SQL)

Other Resources

File States
Understanding Files and Filegroups

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
14 April 2006	New content: Added required permissions.