database_id

int

ID of the database to which this file applies. The master database_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.

type

tinyint

File type:

0 = Rows. (Includes files of full-text catalogs that are upgraded to or created in SQL Server 2008.)

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; full-text catalogs that are upgraded to or created in SQL Server 2008 will report a file type 0.)

type_desc

nvarchar(60)

Description of the file type:

ROWS (Includes files of full-text catalogs that are upgraded to or created in SQL Server 2008.)

LOG

FILESTREAM

FULLTEXT (Full-text catalogs earlier than SQL Server 2008.)

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.

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.